Help with next date in SQL query

  • Tyekhan (5/30/2013)


    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.

    WHICH ID? Certainly not the HistoryID, right?

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

  • Sorry the PID in the table that is unique ID per customer. --- [PID] [int] NOT NULL,

    Jeff Moden (5/30/2013)


    Tyekhan (5/30/2013)


    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.

    WHICH ID? Certainly not the HistoryID, right?

  • Hope this helps a bit,

    --===== If the test table already exists, drop it

    IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL

    DROP TABLE #mytable

    --===== Create the test table with

    CREATE TABLE #mytable

    (

    [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,

    --===== Insert the test data into the test table

    INSERT INTO #History

    (ID, Name, CallDateTime, RC, Num)

    SELECT '26241','Carr_CHS_20120227','Feb 27 2012 2:07PM','Called','01912' UNION ALL

    SELECT '26263','Carr_CHS_20120227','Feb 27 2012 2:10PM','Called','01443' UNION ALL

    SELECT '39395','Carr_CMM_20120227','Feb 27 2012 2:14PM','Called','01513' UNION ALL

    SELECT '27237','Carr_CHS_20120227','Feb 27 2012 4:14PM','Called','01519' UNION ALL

    SELECT '27254','Carr_CHS_20120227','Feb 27 2012 4:16PM','Called','01270' UNION ALL

    SELECT '27440','Carr_CHS_20120227','Feb 27 2012 4:34PM','Called','01938' UNION ALL

    SELECT '40522','Carr_CMM_20120227','Feb 27 2012 4:48PM','Called','01924' UNION ALL

    SELECT '40528','Carr_CMM_20120227','Feb 27 2012 4:48PM','Called','01875' UNION ALL

    SELECT '40595','Carr_CMM_20120227','Feb 27 2012 4:58PM','Called','34849' UNION ALL

    SELECT '27712','Carr_CHS_20120227','Feb 27 2012 5:03PM','Called','30476' UNION ALL

    SELECT '40654','Carr_CMM_20120227','Feb 27 2012 5:08PM','Called','82228' UNION ALL

    SELECT '40664','Carr_CMM_20120227','Feb 27 2012 5:09PM','Called','13048' UNION ALL

    SELECT '40677','Carr_CMM_20120227','Feb 27 2012 5:11PM','Called','81410' UNION ALL

    SELECT '40740','Carr_CMM_20120227','Feb 27 2012 5:19PM','Called','52379' UNION ALL

    SELECT '28262','Carr_CHS_20120227','Feb 27 2012 6:17PM','Called','01612' UNION ALL

    SELECT '28263','Carr_CHS_20120227','Feb 27 2012 6:17PM','Called','0148227' UNION ALL

    SELECT '41207','Carr_CMM_20120227','Feb 27 2012 6:47PM','Called','02891209' UNION ALL

    SELECT '28654','Carr_CHS_20120227','Feb 27 2012 7:00PM','Called','014240' UNION ALL

    SELECT '41393','Carr_CMM_20120227','Feb 27 2012 7:07PM','Called','01642198' UNION ALL

    SELECT '30313','Carr_CHS_20120227','Feb 28 2012 12:36PM','Called','01142478'

    I'm using Server 2008 Management Studio.

  • Tyekhan (6/4/2013)


    Hope this helps a bit,

    --===== If the test table already exists, drop it

    IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL

    DROP TABLE #mytable

    --===== Create the test table with

    CREATE TABLE #mytable

    (

    [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,

    --===== Insert the test data into the test table

    INSERT INTO #History

    (ID, Name, CallDateTime, RC, Num)

    SELECT '26241','Carr_CHS_20120227','Feb 27 2012 2:07PM','Called','01912' UNION ALL

    SELECT '26263','Carr_CHS_20120227','Feb 27 2012 2:10PM','Called','01443' UNION ALL

    SELECT '39395','Carr_CMM_20120227','Feb 27 2012 2:14PM','Called','01513' UNION ALL

    SELECT '27237','Carr_CHS_20120227','Feb 27 2012 4:14PM','Called','01519' UNION ALL

    SELECT '27254','Carr_CHS_20120227','Feb 27 2012 4:16PM','Called','01270' UNION ALL

    SELECT '27440','Carr_CHS_20120227','Feb 27 2012 4:34PM','Called','01938' UNION ALL

    SELECT '40522','Carr_CMM_20120227','Feb 27 2012 4:48PM','Called','01924' UNION ALL

    SELECT '40528','Carr_CMM_20120227','Feb 27 2012 4:48PM','Called','01875' UNION ALL

    SELECT '40595','Carr_CMM_20120227','Feb 27 2012 4:58PM','Called','34849' UNION ALL

    SELECT '27712','Carr_CHS_20120227','Feb 27 2012 5:03PM','Called','30476' UNION ALL

    SELECT '40654','Carr_CMM_20120227','Feb 27 2012 5:08PM','Called','82228' UNION ALL

    SELECT '40664','Carr_CMM_20120227','Feb 27 2012 5:09PM','Called','13048' UNION ALL

    SELECT '40677','Carr_CMM_20120227','Feb 27 2012 5:11PM','Called','81410' UNION ALL

    SELECT '40740','Carr_CMM_20120227','Feb 27 2012 5:19PM','Called','52379' UNION ALL

    SELECT '28262','Carr_CHS_20120227','Feb 27 2012 6:17PM','Called','01612' UNION ALL

    SELECT '28263','Carr_CHS_20120227','Feb 27 2012 6:17PM','Called','0148227' UNION ALL

    SELECT '41207','Carr_CMM_20120227','Feb 27 2012 6:47PM','Called','02891209' UNION ALL

    SELECT '28654','Carr_CHS_20120227','Feb 27 2012 7:00PM','Called','014240' UNION ALL

    SELECT '41393','Carr_CMM_20120227','Feb 27 2012 7:07PM','Called','01642198' UNION ALL

    SELECT '30313','Carr_CHS_20120227','Feb 28 2012 12:36PM','Called','01142478'

    I'm using Server 2008 Management Studio.

    ok....will throw in an idea for you to consider.

    first off...the script you posted above will not work...you need to test this before you post.

    I leave you to sort that..

    second you fail to provide data for all columns

    third...your sample of dates (days) is only two dates....???

    anyways, based on what I think you need here is some code to play with

    --===== If the test table already exists, drop it

    IF OBJECT_ID('TempDB..History','U') IS NOT NULL

    DROP TABLE History

    --===== Create the test table with

    CREATE TABLE History

    (

    [HistoryID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,

    [SystemID] [tinyint] NULL,

    [PID] [int] NULL,

    [Name] [varchar](35) NOT NULL,

    [Source] [varchar](32) NULL,

    [SourceField] [varchar](32) NULL,

    [SourceID] [varchar](32) NULL,

    [CallDateTime] [datetime] NULL,

    [Num] [varchar](15) NULL,

    [CallData] [varchar](240) NULL,

    [ANI] [varchar](15) NULL,

    [RC] [varchar](10) NULL,

    [ID] [int] NULL,

    )

    --===== Insert the test data into the test table

    INSERT INTO History

    (ID, Name, CallDateTime, RC, Num)

    SELECT '26241','Carr_CHS_20120227','Feb 27 2012 2:07PM','Called','01912' UNION ALL

    SELECT '26263','Carr_CHS_20120227','Feb 27 2012 2:10PM','Called','01443' UNION ALL

    SELECT '39395','Carr_CMM_20120227','Feb 27 2012 2:14PM','Called','01513' UNION ALL

    SELECT '27237','Carr_CHS_20120227','Feb 27 2012 4:14PM','Called','01519' UNION ALL

    SELECT '27254','Carr_CHS_20120227','Feb 27 2012 4:16PM','Called','01270' UNION ALL

    SELECT '27440','Carr_CHS_20120227','Feb 27 2012 4:34PM','Called','01938' UNION ALL

    SELECT '40522','Carr_CMM_20120227','Feb 27 2012 4:48PM','Called','01924' UNION ALL

    SELECT '40528','Carr_CMM_20120227','Feb 27 2012 4:48PM','Called','01875' UNION ALL

    SELECT '40595','Carr_CMM_20120227','Feb 27 2012 4:58PM','Called','34849' UNION ALL

    SELECT '27712','Carr_CHS_20120227','Feb 27 2012 5:03PM','Called','30476' UNION ALL

    SELECT '40654','Carr_CMM_20120227','Feb 27 2012 5:08PM','Called','82228' UNION ALL

    SELECT '40664','Carr_CMM_20120227','Feb 27 2012 5:09PM','Called','13048' UNION ALL

    SELECT '40677','Carr_CMM_20120227','Feb 27 2012 5:11PM','Called','81410' UNION ALL

    SELECT '40740','Carr_CMM_20120227','Feb 27 2012 5:19PM','Called','52379' UNION ALL

    SELECT '28262','Carr_CHS_20120227','Feb 27 2012 6:17PM','Called','01612' UNION ALL

    SELECT '28263','Carr_CHS_20120227','Feb 27 2012 6:17PM','Called','0148227' UNION ALL

    SELECT '41207','Carr_CMM_20120227','Feb 27 2012 6:47PM','Called','02891209' UNION ALL

    SELECT '28654','Carr_CHS_20120227','Feb 27 2012 7:00PM','Called','014240' UNION ALL

    SELECT '41393','Carr_CMM_20120227','Feb 27 2012 7:07PM','Called','01642198' UNION ALL

    SELECT '30313','Carr_CHS_20120227','Feb 28 2012 12:36PM','Called','01142478'

    SELECT HistoryID, SystemID, PID, Name, Source, SourceField, SourceID, CallDateTime, Num, CallData, ANI, RC, ID

    FROM History

    ORDER BY Name, CallDateTime

    ;

    with CTE as

    (

    SELECT ID, Name, CallDateTime, Num, RC, row_number() over(PARTITION BY [Name] order by calldatetime ) as rn

    FROM History

    WHERE (CallDateTime > CONVERT(DATETIME, '2012-02-27 16:48:00.000', 102)) AND (rc = 'Called')

    )

    SELECT firstcall.ID

    , firstcall.Name

    , firstcall.CallDateTime

    , firstcall.Num

    , firstcall.RC

    , nextcall.CallDateTime AS NextCallDateTime

    , datediff(d,firstcall.CallDateTime,nextcall.CallDateTime) as numberofdays

    FROM

    cte AS firstcall INNER JOIN cte AS nextcall

    ON firstcall.Name = nextcall.Name

    WHERE (firstcall.rn = 1)

    AND (nextcall.rn = 2);

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • Thanks

    I have added PID to the data below The PID is unique ID per customer so on the below i want to see

    PID = 22

    calldatetime 25/02/2012

    NextCallDateTime = 28/02/2013

    numberofdays = 3

    --===== If the test table already exists, drop it

    IF OBJECT_ID('TempDB..HistoryTEST','U') IS NOT NULL

    DROP TABLE HistoryTEST

    --===== Create the test table with

    CREATE TABLE HistoryTEST

    (

    [HistoryTESTID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,

    [SystemID] [tinyint] NULL,

    [PID] [int] NULL,

    [Name] [varchar](35) NOT NULL,

    [Source] [varchar](32) NULL,

    [SourceField] [varchar](32) NULL,

    [SourceID] [varchar](32) NULL,

    [CallDateTime] [datetime] NULL,

    [Num] [varchar](15) NULL,

    [CallData] [varchar](240) NULL,

    [ANI] [varchar](15) NULL,

    [RC] [varchar](10) NULL,

    [ID] [int] NULL,

    )

    --===== Insert the test data into the test table

    INSERT INTO HistoryTEST

    (ID, Name, CallDateTime, RC, Num, PID)

    SELECT '26241','Carr_CHS_20120227','Feb 25 2012 3:07PM','Called','01912','22' UNION ALL

    SELECT '28252','Carr_CHS_20120227','Feb 28 2012 4:07PM','PCO','01912','22' UNION ALL

    SELECT '26263','Carr_CHS_20120227','Feb 27 2012 2:10PM','Called','01443','23' UNION ALL

    SELECT '39395','Carr_CMM_20120227','Feb 27 2012 2:14PM','Called','01513','24' UNION ALL

    SELECT '27237','Carr_CHS_20120227','Feb 27 2012 4:14PM','Called','01519','25' UNION ALL

    SELECT '27254','Carr_CHS_20120227','Feb 27 2012 4:16PM','Called','01270','26' UNION ALL

    SELECT '27440','Carr_CHS_20120227','Feb 27 2012 4:34PM','Called','01938','27' UNION ALL

    SELECT '40522','Carr_CMM_20120227','Feb 27 2012 4:48PM','Called','01924','28' UNION ALL

    SELECT '40528','Carr_CMM_20120227','Feb 27 2012 4:48PM','Called','01875','29' UNION ALL

    SELECT '40595','Carr_CMM_20120227','Feb 27 2012 4:58PM','Called','34849','30' UNION ALL

    SELECT '27712','Carr_CHS_20120227','Feb 27 2012 5:03PM','Called','30476','31' UNION ALL

    SELECT '40654','Carr_CMM_20120227','Feb 27 2012 5:08PM','Called','82228','32' UNION ALL

    SELECT '40664','Carr_CMM_20120227','Feb 27 2012 5:09PM','Called','13048','33' UNION ALL

    SELECT '40677','Carr_CMM_20120227','Feb 27 2012 5:11PM','Called','81410','34' UNION ALL

    SELECT '40740','Carr_CMM_20120227','Feb 27 2012 5:19PM','Called','52379','35' UNION ALL

    SELECT '28262','Carr_CHS_20120227','Feb 27 2012 6:17PM','Called','01612','36' UNION ALL

    SELECT '28265','Carr_CHS_20120227','Mar 2 2012 6:17PM','Other','01612' ,'36'UNION ALL

    SELECT '28263','Carr_CHS_20120227','Feb 27 2012 6:17PM','Called','0148227','37' UNION ALL

    SELECT '41207','Carr_CMM_20120227','Feb 27 2012 6:47PM','Called','02891209','38' UNION ALL

    SELECT '28654','Carr_CHS_20120227','Feb 27 2012 7:00PM','Called','014240','39' UNION ALL

    SELECT '28654','Carr_CHS_20120227','Feb 28 2012 7:00PM','KKT','014240','40' UNION ALL

    SELECT '41393','Carr_CMM_20120227','Feb 27 2012 7:07PM','Called','01642198','41' UNION ALL

    SELECT '41345','Carr_CMM_20120227','Mar 4 2012 7:07PM','NIG','01642198' ,'41'UNION ALL

    SELECT '30313','Carr_CHS_20120227','Feb 28 2012 12:36PM','Called','01142478','42'

    SELECT HistoryTESTID, SystemID, PID, Name, Source, SourceField, SourceID, CallDateTime, Num, CallData, ANI, RC, ID

    FROM HistoryTEST

    ORDER BY Name, CallDateTime

    ;

    with CTE as

    (

    SELECT ID, PID,Name, CallDateTime, Num, RC, row_number() over(PARTITION BY [Name] order by calldatetime ) as rn

    FROM HistoryTEST

    WHERE (CallDateTime > CONVERT(DATETIME, '2012-02-27 16:48:00.000', 102)) AND (rc = 'Called')

    )

    SELECT firstcall.ID

    , firstcall.PID

    , firstcall.Name

    , firstcall.CallDateTime

    , firstcall.Num

    , firstcall.RC

    , nextcall.CallDateTime AS NextCallDateTime

    , datediff(d,firstcall.CallDateTime,nextcall.CallDateTime) as numberofdays

    FROM

    cte AS firstcall INNER JOIN cte AS nextcall

    ON firstcall.Name = nextcall.Name

    WHERE (firstcall.rn = 1)

    AND (nextcall.rn = 2);

  • suggest you revisit the solution that Jeff Moden gave you.....just swap out the table name and I think you will need to replace "Num" with "PID"...that should give you what you want.

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • I have replace name with PID, but its coming up blank

    Any ideas

    --===== If the test table already exists, drop it

    IF OBJECT_ID('TempDB..HistoryTEST','U') IS NOT NULL

    DROP TABLE HistoryTEST

    --===== Create the test table with

    CREATE TABLE HistoryTEST

    (

    [HistoryTESTID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,

    [SystemID] [tinyint] NULL,

    [PID] [int] NULL,

    [Name] [varchar](35) NOT NULL,

    [Source] [varchar](32) NULL,

    [SourceField] [varchar](32) NULL,

    [SourceID] [varchar](32) NULL,

    [CallDateTime] [datetime] NULL,

    [Num] [varchar](15) NULL,

    [CallData] [varchar](240) NULL,

    [ANI] [varchar](15) NULL,

    [RC] [varchar](10) NULL,

    [ID] [int] NULL,

    )

    --===== Insert the test data into the test table

    INSERT INTO HistoryTEST

    (ID, Name, CallDateTime, RC, Num, PID)

    SELECT '26241','Carr_CHS_20120227','Feb 25 2012 3:07PM','Called','01912','22' UNION ALL

    SELECT '28252','Carr_CHS_20120227','Feb 28 2012 4:07PM','PCO','01912','22' UNION ALL

    SELECT '26263','Carr_CHS_20120227','Feb 27 2012 2:10PM','Called','01443','23' UNION ALL

    SELECT '39395','Carr_CMM_20120227','Feb 27 2012 2:14PM','Called','01513','24' UNION ALL

    SELECT '27237','Carr_CHS_20120227','Feb 27 2012 4:14PM','Called','01519','25' UNION ALL

    SELECT '27254','Carr_CHS_20120227','Feb 27 2012 4:16PM','Called','01270','26' UNION ALL

    SELECT '27440','Carr_CHS_20120227','Feb 27 2012 4:34PM','Called','01938','27' UNION ALL

    SELECT '40522','Carr_CMM_20120227','Feb 27 2012 4:48PM','Called','01924','28' UNION ALL

    SELECT '40528','Carr_CMM_20120227','Feb 27 2012 4:48PM','Called','01875','29' UNION ALL

    SELECT '40595','Carr_CMM_20120227','Feb 27 2012 4:58PM','Called','34849','30' UNION ALL

    SELECT '27712','Carr_CHS_20120227','Feb 27 2012 5:03PM','Called','30476','31' UNION ALL

    SELECT '40654','Carr_CMM_20120227','Feb 27 2012 5:08PM','Called','82228','32' UNION ALL

    SELECT '40664','Carr_CMM_20120227','Feb 27 2012 5:09PM','Called','13048','33' UNION ALL

    SELECT '40677','Carr_CMM_20120227','Feb 27 2012 5:11PM','Called','81410','34' UNION ALL

    SELECT '40740','Carr_CMM_20120227','Feb 27 2012 5:19PM','Called','52379','35' UNION ALL

    SELECT '28262','Carr_CHS_20120227','Feb 27 2012 6:17PM','Called','01612','36' UNION ALL

    SELECT '28265','Carr_CHS_20120227','Mar 2 2012 6:17PM','Other','01612' ,'36'UNION ALL

    SELECT '28263','Carr_CHS_20120227','Feb 27 2012 6:17PM','Called','0148227','37' UNION ALL

    SELECT '41207','Carr_CMM_20120227','Feb 27 2012 6:47PM','Called','02891209','38' UNION ALL

    SELECT '28654','Carr_CHS_20120227','Feb 27 2012 7:00PM','Called','014240','39' UNION ALL

    SELECT '28654','Carr_CHS_20120227','Feb 28 2012 7:00PM','KKT','014240','40' UNION ALL

    SELECT '41393','Carr_CMM_20120227','Feb 27 2012 7:07PM','Called','01642198','41' UNION ALL

    SELECT '41345','Carr_CMM_20120227','Mar 4 2012 7:07PM','NIG','01642198' ,'41'UNION ALL

    SELECT '30313','Carr_CHS_20120227','Feb 28 2012 12:36PM','Called','01142478','42'

    SELECT HistoryTESTID, SystemID, PID, Name, Source, SourceField, SourceID, CallDateTime, Num, CallData, ANI, RC, ID

    FROM HistoryTEST

    ORDER BY PID, CallDateTime

    ;

    with CTE as

    (

    SELECT ID, PID,Name, CallDateTime, Num, RC, row_number() over(PARTITION BY [PID] order by calldatetime ) as rn

    FROM HistoryTEST

    WHERE (CallDateTime > CONVERT(DATETIME, '2012-02-27 16:48:00.000', 102)) AND (rc = 'Called')

    )

    SELECT firstcall.ID

    , firstcall.PID

    , firstcall.Name

    , firstcall.CallDateTime

    , firstcall.Num

    , firstcall.RC

    , nextcall.CallDateTime AS NextCallDateTime

    , datediff(d,firstcall.CallDateTime,nextcall.CallDateTime) as numberofdays

    FROM

    cte AS firstcall INNER JOIN cte AS nextcall

    ON firstcall.PID = nextcall.PID

    WHERE (firstcall.rn = 1)

    AND (nextcall.rn = 2);

    J Livingston SQL (6/5/2013)


    suggest you revisit the solution that Jeff Moden gave you.....just swap out the table name and I think you will need to replace "Num" with "PID"...that should give you what you want.

  • that's not the code from Jeff Moden...that's mine...go back a few posts

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • Tired that code again but still not working,

    Randomizing ContactID's and CallDateTime's...

    (1000000 row(s) affected)

    Building/populating the #HistoryTest table...

    Msg 208, Level 16, State 1, Line 19

    Invalid object name 'AdventureWorks.Person.Contact'.

    J Livingston SQL (6/5/2013)


    that's not the code from Jeff Moden...that's mine...go back a few posts

  • Tyekhan (6/5/2013)


    Tired that code again but still not working,

    Randomizing ContactID's and CallDateTime's...

    (1000000 row(s) affected)

    Building/populating the #HistoryTest table...

    Msg 208, Level 16, State 1, Line 19

    Invalid object name 'AdventureWorks.Person.Contact'.

    J Livingston SQL (6/5/2013)


    that's not the code from Jeff Moden...that's mine...go back a few posts

    I just haven't had the time to come up with good example data. You should take the time to either install the AdventureWorks database or read the solution code I posted to warp it to your needs or read the first link in my signature line below for how to post some usable data for us to demonstrate a solution with.

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

  • Jeff Moden (6/5/2013)


    Tyekhan (6/5/2013)


    Tired that code again but still not working,

    Randomizing ContactID's and CallDateTime's...

    (1000000 row(s) affected)

    Building/populating the #HistoryTest table...

    Msg 208, Level 16, State 1, Line 19

    Invalid object name 'AdventureWorks.Person.Contact'.

    J Livingston SQL (6/5/2013)


    that's not the code from Jeff Moden...that's mine...go back a few posts

    I just haven't had the time to come up with good example data. You should take the time to either install the AdventureWorks database or read the solution code I posted to warp it to your needs or read the first link in my signature line below for how to post some usable data for us to demonstrate a solution with.

    Jeffs code suitably altered to your needs

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

    -- Solve the problem

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

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

    @StartDay DATETIME,

    @nextday DATETIME

    ;

    SELECT @pGivenDT = '2012-02-25',

    @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 PID,CallDateTime),

    ID, PID,Name, CallDateTime, Num, RC

    FROM HistoryTest

    WHERE CallDateTime >= @StartDay

    AND CallDateTime < @nextday

    ),

    cteFindNext AS

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

    SELECT PID,

    CallDateTime = MIN(CallDateTime)

    FROM HistoryTest

    WHERE CallDateTime >= @nextday

    GROUP BY PID

    )

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

    SELECT g.PID,

    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.PID = g.PID

    WHERE g.RowNum = 1

    by the way..you really do need to understand the code and how it works...you after all are going to have to support it. If you care to read all the comments Jeff has kindly included...this will give you a good start...post back with questions as necessary.

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

Viewing 11 posts - 16 through 25 (of 25 total)

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