Help with next date in SQL query

  • 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 NameCallDateTime Num RC

    3936803Name101/05/2013 11:16:2784Called

    5211387Name201/05/2013 12:14:21604Called

    5185689Name301/05/2013 12:15:28298Called

    4811923Name401/05/2013 12:29:36170Called

    but i also want it to show the below,

    ID Name CallDateTime Num RC Next CallDateTimeTotal Number Of Days

    3936803Name101/05/2013 11:16 84Called 04/05/2013 11:16 3

    5211387Name201/05/2013 12:14 604Called 04/05/2013 12:14 3

    5185689Name301/05/2013 12:15 298Called 04/05/2013 12:15 3

    4811923Name401/05/2013 12:29 170Called 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,

  • 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 ?

  • 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 ?

  • 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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • 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.

  • 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.

  • 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.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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?

  • 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.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • No solution found that works yet, Still need help πŸ™

    Jeff Moden (5/25/2013)


    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?

  • Tyekhan (5/25/2013)


    No solution found that works yet, Still need help πŸ™

    Jeff Moden (5/25/2013)


    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?

    Ok... First, I suspect that part of the reason why most folks were reluctant to help you is because there was no test data and you didn't explain things real well. For example, it's suicide to try to use name as a unique key but you didn't say anything about the other columns being unique by person. It makes it real tough to come up with something decent. Even I made an assumption that you can easily change... I assumed that NUM was going to be unique Could be wrong there and you'd need to change it but you can't correct a blank piece of paper.

    Please see the first link in my signature line before you post another problem. If you follow the suggestions in that article for how to post data, a whole lot more people will try to help a whole lot more quickly.

    Here's all of the code. As is normal for me, the details are in the comments. It includes the construction of a million row test table just to show it runs fairly fast.

    First, the test data with some suggested indexes.

    --=======================================================================================

    -- Build a million row test table for this problem.

    -- Nothing in this section is a part of a solution except maybe for indexes.

    -- This takes less than a minute on my 11 year old desktop box.

    -- There is a very small chance you could get a duplicate error in this section

    -- If that happens, just run the section again.

    --=======================================================================================

    --===== Conditionally drop the test table(s) to make reruns easier in SSMS

    IF OBJECT_ID('tempdb..#Randomize','U') IS NOT NULL DROP TABLE #Randomize;

    IF OBJECT_ID('tempdb..#History' ,'U') IS NOT NULL DROP TABLE #History;

    GO

    --===== Create some random call dates from random ContactID's.

    -- This generates random call dates from 2000-01-01 through 2013-05-25 (not 26).

    RAISERROR('Randomizing ContactID''s and CallDateTime''s...',0,1) WITH NOWAIT;

    SELECT TOP 1000000

    ContactID = ISNULL(ABS(CHECKSUM(NEWID()))%19977+1,0),

    CallDateTime = RAND(CHECKSUM(NEWID())) * DATEDIFF(dd,'20000101','20130526')

    + CAST('20000101' AS DATETIME)

    INTO #Randomize

    FROM sys.all_columns ac1

    CROSS JOIN sys.all_columns ac2

    ;

    --===== Create/Populate the History table by joining a known source to the random data.

    -- The ISNULL's make the columns NOT NULL.

    -- Also notice the ORDER BY is building the table in the same order expected

    -- in real life so the HistoryID simulates what happens in the real table.

    -- The History table may end up with less than a million rows because the source

    -- table has gaps in the ContactID's like any real table.

    RAISERROR('Building/populating the #History table...',0,1) WITH NOWAIT;

    SELECT HistoryID = IDENTITY(INT,1,1),

    Name = ISNULL(c.LastName,'') + ',' + ISNULL(c.FirstName,''),

    CallDateTime = ISNULL(r.CallDateTime,0),

    Num = ISNULL(c.Phone,''),

    RC = ISNULL('Called','')

    INTO #History

    FROM AdventureWorks.Person.Contact c

    JOIN #Randomize r

    ON c.ContactID = r.ContactID

    ORDER BY r.CallDateTime, r.ContactID

    ;

    --===== Add a unique clustered index to the #History table.

    -- Note that this is NOT a PK.

    RAISERROR('Adding the UNIQUE Clustered Index...',0,1) WITH NOWAIT;

    CREATE UNIQUE CLUSTERED INDEX IXC_#History_CallDateTime_HistoryID

    ON #History (CallDateTime,HistoryID)

    ;

    --===== Add a non-clustered PK

    RAISERROR('Adding the NON-Clustered PK...',0,1) WITH NOWAIT;

    ALTER TABLE #History

    ADD PRIMARY KEY NONCLUSTERED (Num,CallDateTime) WITH FILLFACTOR = 90

    ;

    ... and this is one solution. I used "Divide'n'Conquer" methods so that you can run the code within each CTE separately to see what's happening. The code is physically a bit longer than what someone else might come up with but it's damned easy to understand and troubleshoot this way. The performance isn't bad, either.

    --=======================================================================================

    -- Solve the problem

    --=======================================================================================

    DECLARE @pGivenDT DATETIME, --This could be a parameter for a stored proc.

    @StartDay DATETIME,

    @nextday DATETIME

    ;

    SELECT @pGivenDT = '2013-05-01',

    @StartDay = DATEDIFF(dd,0,@pGivenDT),

    @nextday = DATEADD(dd,1,@StartDay)

    ;

    WITH

    cteFindGiven AS

    ( --=== Find the calls on the day desired

    SELECT RowNum = ROW_NUMBER() OVER (PARTITION BY Num ORDER BY Num,CallDateTime),

    HistoryID,

    Name,

    CallDateTime,

    Num,

    RC

    FROM #History

    WHERE CallDateTime >= @StartDay

    AND CallDateTime < @nextday

    ),

    cteFindNext AS

    ( --=== Find the first call for each Num after the given day.

    SELECT Num,

    CallDateTime = MIN(CallDateTime)

    FROM #History

    WHERE CallDateTime >= @nextday

    GROUP BY Num

    )

    --===== Join the two CTE's to produce the desired output

    SELECT g.HistoryID,

    g.Name,

    g.CallDateTime,

    g.Num,

    g.RC,

    [Next CallDateTime] = n.CallDateTime,

    [Total Number of Days] = DATEDIFF(dd,g.CallDateTime,n.CallDateTime)

    FROM cteFindGiven g

    LEFT JOIN cteFindNext n --Remove "LEFT" to get just the matches after QA

    ON n.Num = g.Num

    WHERE g.RowNum = 1

    ;

    Like I said, if the "Num" column is the wrong thing to consider to be unique, then we'll have to change a couple of indexes and a couple of column names.

    --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.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thank you for the code i have just tested it, its coming up with the below code error,

    'Randomizing ContactID's and CallDateTime's...

    (1000000 row(s) affected)

    Building/populating the #History table...

    Msg 208, Level 16, State 1, Line 19

    Invalid object name 'AdventureWorks.Person.Contact'.,

    Hope you could help with the error.

    Jeff Moden (5/25/2013)


    Tyekhan (5/25/2013)


    No solution found that works yet, Still need help πŸ™

    Jeff Moden (5/25/2013)


    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?

    Ok... First, I suspect that part of the reason why most folks were reluctant to help you is because there was no test data and you didn't explain things real well. For example, it's suicide to try to use name as a unique key but you didn't say anything about the other columns being unique by person. It makes it real tough to come up with something decent. Even I made an assumption that you can easily change... I assumed that NUM was going to be unique Could be wrong there and you'd need to change it but you can't correct a blank piece of paper.

    Please see the first link in my signature line before you post another problem. If you follow the suggestions in that article for how to post data, a whole lot more people will try to help a whole lot more quickly.

    Here's all of the code. As is normal for me, the details are in the comments. It includes the construction of a million row test table just to show it runs fairly fast.

    First, the test data with some suggested indexes.

    --=======================================================================================

    -- Build a million row test table for this problem.

    -- Nothing in this section is a part of a solution except maybe for indexes.

    -- This takes less than a minute on my 11 year old desktop box.

    -- There is a very small chance you could get a duplicate error in this section

    -- If that happens, just run the section again.

    --=======================================================================================

    --===== Conditionally drop the test table(s) to make reruns easier in SSMS

    IF OBJECT_ID('tempdb..#Randomize','U') IS NOT NULL DROP TABLE #Randomize;

    IF OBJECT_ID('tempdb..#History' ,'U') IS NOT NULL DROP TABLE #History;

    GO

    --===== Create some random call dates from random ContactID's.

    -- This generates random call dates from 2000-01-01 through 2013-05-25 (not 26).

    RAISERROR('Randomizing ContactID''s and CallDateTime''s...',0,1) WITH NOWAIT;

    SELECT TOP 1000000

    ContactID = ISNULL(ABS(CHECKSUM(NEWID()))%19977+1,0),

    CallDateTime = RAND(CHECKSUM(NEWID())) * DATEDIFF(dd,'20000101','20130526')

    + CAST('20000101' AS DATETIME)

    INTO #Randomize

    FROM sys.all_columns ac1

    CROSS JOIN sys.all_columns ac2

    ;

    --===== Create/Populate the History table by joining a known source to the random data.

    -- The ISNULL's make the columns NOT NULL.

    -- Also notice the ORDER BY is building the table in the same order expected

    -- in real life so the HistoryID simulates what happens in the real table.

    -- The History table may end up with less than a million rows because the source

    -- table has gaps in the ContactID's like any real table.

    RAISERROR('Building/populating the #History table...',0,1) WITH NOWAIT;

    SELECT HistoryID = IDENTITY(INT,1,1),

    Name = ISNULL(c.LastName,'') + ',' + ISNULL(c.FirstName,''),

    CallDateTime = ISNULL(r.CallDateTime,0),

    Num = ISNULL(c.Phone,''),

    RC = ISNULL('Called','')

    INTO #History

    FROM AdventureWorks.Person.Contact c

    JOIN #Randomize r

    ON c.ContactID = r.ContactID

    ORDER BY r.CallDateTime, r.ContactID

    ;

    --===== Add a unique clustered index to the #History table.

    -- Note that this is NOT a PK.

    RAISERROR('Adding the UNIQUE Clustered Index...',0,1) WITH NOWAIT;

    CREATE UNIQUE CLUSTERED INDEX IXC_#History_CallDateTime_HistoryID

    ON #History (CallDateTime,HistoryID)

    ;

    --===== Add a non-clustered PK

    RAISERROR('Adding the NON-Clustered PK...',0,1) WITH NOWAIT;

    ALTER TABLE #History

    ADD PRIMARY KEY NONCLUSTERED (Num,CallDateTime) WITH FILLFACTOR = 90

    ;

    ... and this is one solution. I used "Divide'n'Conquer" methods so that you can run the code within each CTE separately to see what's happening. The code is physically a bit longer than what someone else might come up with but it's damned easy to understand and troubleshoot this way. The performance isn't bad, either.

    --=======================================================================================

    -- Solve the problem

    --=======================================================================================

    DECLARE @pGivenDT DATETIME, --This could be a parameter for a stored proc.

    @StartDay DATETIME,

    @nextday DATETIME

    ;

    SELECT @pGivenDT = '2013-05-01',

    @StartDay = DATEDIFF(dd,0,@pGivenDT),

    @nextday = DATEADD(dd,1,@StartDay)

    ;

    WITH

    cteFindGiven AS

    ( --=== Find the calls on the day desired

    SELECT RowNum = ROW_NUMBER() OVER (PARTITION BY Num ORDER BY Num,CallDateTime),

    HistoryID,

    Name,

    CallDateTime,

    Num,

    RC

    FROM #History

    WHERE CallDateTime >= @StartDay

    AND CallDateTime < @nextday

    ),

    cteFindNext AS

    ( --=== Find the first call for each Num after the given day.

    SELECT Num,

    CallDateTime = MIN(CallDateTime)

    FROM #History

    WHERE CallDateTime >= @nextday

    GROUP BY Num

    )

    --===== Join the two CTE's to produce the desired output

    SELECT g.HistoryID,

    g.Name,

    g.CallDateTime,

    g.Num,

    g.RC,

    [Next CallDateTime] = n.CallDateTime,

    [Total Number of Days] = DATEDIFF(dd,g.CallDateTime,n.CallDateTime)

    FROM cteFindGiven g

    LEFT JOIN cteFindNext n --Remove "LEFT" to get just the matches after QA

    ON n.Num = g.Num

    WHERE g.RowNum = 1

    ;

    Like I said, if the "Num" column is the wrong thing to consider to be unique, then we'll have to change a couple of indexes and a couple of column names.

  • So, any feedback on my previous comment of...

    Like I said, if the "Num" column is the wrong thing to consider to be unique, then we'll have to change a couple of indexes and a couple of column names.

    --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.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Yes the num column is unique, but the best thing to use would be ID as the same records would have the same ID.

    Jeff Moden (5/30/2013)


    So, any feedback on my previous comment of...

    Like I said, if the "Num" column is the wrong thing to consider to be unique, then we'll have to change a couple of indexes and a couple of column names.

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply