Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help with next date in SQL query


Help with next date in SQL query

Author
Message
Tyekhan
Tyekhan
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 103
What I need is to be able to find out when a customer next called in from the date I have select with the next date & time on any Purchase, does don't need to be same. It’s like using MIN & MAX but that does not work on this query as it select the same date & time for both.

SELECT ID, Name, CallDateTime, Num, RC
FROM History
WHERE (CallDateTime > CONVERT(DATETIME, '2013-05-01 00:00:00', 102)) AND (Outcome = 'Called')

As you can see in the query above that all the data is in one overall table called History, this records all the purchases.

So I want to know that if a customer after the 1/05/2013 called in with the outcome of called what was he next purchases date, some customer might not have come so that can stay blank.

So the query is like this now

ID Name CallDateTime Num RC
3936803 Name1 01/05/2013 11:16:27 84 Called
5211387 Name2 01/05/2013 12:14:21 604 Called
5185689 Name3 01/05/2013 12:15:28 298 Called
4811923 Name4 01/05/2013 12:29:36 170 Called

but i also want it to show the below,

ID Name CallDateTime Num RC Next CallDateTime Total Number Of Days
3936803 Name1 01/05/2013 11:16 84 Called 04/05/2013 11:16 3
5211387 Name2 01/05/2013 12:14 604 Called 04/05/2013 12:14 3
5185689 Name3 01/05/2013 12:15 298 Called 04/05/2013 12:15 3
4811923 Name4 01/05/2013 12:29 170 Called 04/05/2013 12:29 3

This is the query I have at the moment BUT not show how to add two more columns to it they are next called in date & time after the first called in. I want it to show the next time & date does not matter what the RC code is next visit.

So I want mine output to show the below, adding the next called time & date and how many days it take from the first date to the next date,
alezar
alezar
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 73
Hi,
Could you please post a script to show the structure of History table ?
About Next CallDateTime : This colum already exists in the table or it should be calculated ?
Tyekhan
Tyekhan
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 103
Next CallDateTime information is in the data but its not called Next CallDateTime, its CallDateTime.

So if records 1 CallDateTime was after the select date e.g 01/05/2013 then they revisited on the 05/05/2013 and then 10/05/2013.

on the nextcalldatetime i just want to see CallDateTime as 01/05/2013 & the next CallDateTime as 05/05/2013

[dbo].[History](
[HistoryID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[SystemID] [tinyint] NOT NULL,
[PID] [int] NOT NULL,
[Name] [varchar](35) NOT NULL,
[Source] [varchar](32) NOT NULL,
[SourceField] [varchar](32) NOT NULL,
[SourceID] [varchar](32) NOT NULL,
[CallDateTime] [datetime] NOT NULL,
[Num] [varchar](15) NOT NULL,
[CallData] [varchar](240) NOT NULL,
[ANI] [varchar](15) NOT NULL,
[RC] [varchar](5) NOT NULL,
[ID] [int] NOT NULL,


alezar (5/18/2013)
Hi,
Could you please post a script to show the structure of History table ?
About Next CallDateTime : This colum already exists in the table or it should be calculated ?

Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16550 Visits: 17004
Hi Tyekhan and welcome to the forums. The main issue here is that we have no idea what you are trying to do. We can't see your screen, we don't know the project and we have no idea what your tables are like. Can you please post ddl (create table statements), sample data (insert statements) and desired output based on your sample data? Please take a few minutes and read the first article in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
alezar
alezar
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 73
Hi, as Sean asked it will be great if you could improve description of your req.
What I understand is that if you have two different rows for NAME1, one with calldatetime first of may and the second fifth of may, you want to show both in the result. If more rows are available you want to show always the first date and the following.
Please confirm if this is correct.
Tyekhan
Tyekhan
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 103
Yes that's what i want, if anyone can help me with it.thanks
alezar (5/20/2013)
Hi, as Sean asked it will be great if you could improve description of your req.
What I understand is that if you have two different rows for NAME1, one with calldatetime first of may and the second fifth of may, you want to show both in the result. If more rows are available you want to show always the first date and the following.
Please confirm if this is correct.

Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45038 Visits: 39893
Tyekhan (5/20/2013)
Yes that's what i want, if anyone can help me with it.thanks
alezar (5/20/2013)
Hi, as Sean asked it will be great if you could improve description of your req.
What I understand is that if you have two different rows for NAME1, one with calldatetime first of may and the second fifth of may, you want to show both in the result. If more rows are available you want to show always the first date and the following.
Please confirm if this is correct.


Great. What do you want to do if NAME1 has 3 calls each on the first and fifth of May?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Tyekhan
Tyekhan
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 103
I would like the first call on both day (first & fifth), they will only be 1 for each day any way as it come every 2-4 days.

Jeff Moden (5/20/2013)
Tyekhan (5/20/2013)
Yes that's what i want, if anyone can help me with it.thanks
alezar (5/20/2013)
Hi, as Sean asked it will be great if you could improve description of your req.
What I understand is that if you have two different rows for NAME1, one with calldatetime first of may and the second fifth of may, you want to show both in the result. If more rows are available you want to show always the first date and the following.
Please confirm if this is correct.


Great. What do you want to do if NAME1 has 3 calls each on the first and fifth of May?

Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45038 Visits: 39893
Tyekhan (5/21/2013)
I would like the first call on both day (first & fifth), they will only be 1 for each day any way as it come every 2-4 days.

Jeff Moden (5/20/2013)
Tyekhan (5/20/2013)
Yes that's what i want, if anyone can help me with it.thanks
alezar (5/20/2013)
Hi, as Sean asked it will be great if you could improve description of your req.
What I understand is that if you have two different rows for NAME1, one with calldatetime first of may and the second fifth of may, you want to show both in the result. If more rows are available you want to show always the first date and the following.
Please confirm if this is correct.


Great. What do you want to do if NAME1 has 3 calls each on the first and fifth of May?


Perfect. If no one get's to it before me (I'm on my way to work inn 5), I'll give it a crack tonight.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45038 Visits: 39893
Dammit. I'm sorry. I lost track of this thread and it took me a while to find it again.

Have you found a solution for your problem or do you still need some help?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search