Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Help with next date in SQL query Expand / Collapse
Author
Message
Posted Saturday, May 18, 2013 5:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, June 08, 2013 3:05 AM
Points: 12, Visits: 98
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,
Post #1454245
Posted Saturday, May 18, 2013 6:46 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 27, 2014 3:19 PM
Points: 5, Visits: 26
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 ?
Post #1454297
Posted Monday, May 20, 2013 1:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, June 08, 2013 3:05 AM
Points: 12, Visits: 98
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 ?
Post #1454415
Posted Monday, May 20, 2013 8:14 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:57 PM
Points: 12,004, Visits: 11,035
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)
Post #1454548
Posted Monday, May 20, 2013 1:14 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 27, 2014 3:19 PM
Points: 5, Visits: 26
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.
Post #1454701
Posted Monday, May 20, 2013 5:13 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, June 08, 2013 3:05 AM
Points: 12, Visits: 98
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.
Post #1454774
Posted Monday, May 20, 2013 5:34 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:13 AM
Points: 35,996, Visits: 30,290
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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1454776
Posted Tuesday, May 21, 2013 3:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, June 08, 2013 3:05 AM
Points: 12, Visits: 98
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?
Post #1454891
Posted Tuesday, May 21, 2013 6:25 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:13 AM
Points: 35,996, Visits: 30,290
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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1454965
Posted Saturday, May 25, 2013 2:43 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:13 AM
Points: 35,996, Visits: 30,290
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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1456818
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse