Calculate time difference between two related visitors without using cursor

  • I have a table (see attached) listing visitors IDs, listed more than once, and Arrival DateTime. I need to find those whose gap between arrivals is less than 72 hrs.

    Is it possible to do it without a cursor loop?

    Thanks

  • Please read the second link in my signature (by Jeff Moden)[/url] to help us to help you.

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • Sorry, I thought that info I provided was straightforward.

  • Well, you will find that while some people may be happy to open an Excel spreadsheet and figure things out, the best help will come if you provide readily consumable data and preferably show what you have tried so far...

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • I do not know what would be the better way to present a simple table with 7K+ records (Excel file). The table has 2 columns, ID and Arrival DateTime. Sorted by ID and then by Arrival DateTime. In the past I wrote a code with double cursors iterating over each ID (not for this table and not for this task). I thought that there may be a better way to do it.

    Thanks

  • This is the fragment of the table

    CREATE TABLE [dbo].[Readmission_Table](

    [ID] [float] NULL,

    [ArrivalDateTime] [datetime] NULL

    ) ON [PRIMARY]

    GO

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10000683, CAST(0x0000A66801716A50 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10000683, CAST(0x0000A66900B9AB40 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10000989, CAST(0x0000A64000B778C0 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10000989, CAST(0x0000A65101735680 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10001033, CAST(0x0000A65200F54510 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10001033, CAST(0x0000A66200B91EA0 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10001350, CAST(0x0000A64D01526100 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10001350, CAST(0x0000A66F007055D0 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10002866, CAST(0x0000A63B01305240 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10002866, CAST(0x0000A63E014D6F60 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10003932, CAST(0x0000A65800C27540 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10003932, CAST(0x0000A66201422C90 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10004218, CAST(0x0000A665013357B0 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10004218, CAST(0x0000A66A00EC34C0 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10004220, CAST(0x0000A662006D5060 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10004220, CAST(0x0000A66E0072CEA0 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10005862, CAST(0x0000A66000A06680 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10005862, CAST(0x0000A662009EC0A0 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10006299, CAST(0x0000A64000CC5880 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10006299, CAST(0x0000A645014272E0 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10007852, CAST(0x0000A64F00563E20 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10007852, CAST(0x0000A65600BE1040 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10008474, CAST(0x0000A65500163F50 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10008474, CAST(0x0000A66F00503340 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10009346, CAST(0x0000A6470130DEE0 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10009346, CAST(0x0000A64E009A5BA0 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10009346, CAST(0x0000A651017BDA30 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10009903, CAST(0x0000A64E0123AFE0 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10009903, CAST(0x0000A659015752A0 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10009903, CAST(0x0000A65A0008CA00 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10010401, CAST(0x0000A64E00549840 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10010401, CAST(0x0000A65600305700 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10011580, CAST(0x0000A63E015798F0 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10011580, CAST(0x0000A6570126B550 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10011580, CAST(0x0000A65D0105BFD0 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10012712, CAST(0x0000A64E00AB1CB0 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10012712, CAST(0x0000A65D00041EB0 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10012887, CAST(0x0000A64001586BE0 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10012887, CAST(0x0000A66901236990 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10014218, CAST(0x0000A63A00C6DA40 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10014218, CAST(0x0000A64B00B80560 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10014918, CAST(0x0000A63D01731030 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10014918, CAST(0x0000A64000C9DFB0 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10014918, CAST(0x0000A6490123F630 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10015000, CAST(0x0000A63D00CAF8F0 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10015000, CAST(0x0000A65701388FA0 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10015401, CAST(0x0000A665015BB7A0 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10015401, CAST(0x0000A669015D1730 AS DateTime))

  • valeryk2000 (9/27/2016)


    I do not know what would be the better way to present a simple table with 7K+ records (Excel file). The table has 2 columns, ID and Arrival DateTime. Sorted by ID and then by Arrival DateTime. In the past I wrote a code with double cursors iterating over each ID (not for this table and not for this task). I thought that there may be a better way to do it.

    Thanks

    please read this article.....https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/

    not sure why you think it is necessary to post 7K+ records to explain your problem....but heyho.

    if you could simplify the sample data to demonstrate what you want to achieve by providing sample data that covers ALL scenarios and also to post your expected results based on your sample data...then I am sure you will be pleasantly surprised how quick you get a tried and tested answer.

    as it stands at the moment....you have posted an excel file....so what are responders supposed to do with that...

    1 they need to download it....very few will.

    2 they need to then create a test table in SQL that represents your excel file...making best guesses on datatypes, nulls, constraints, indexes etc

    3 they then need to import your excel file into table created above

    and that is before they start to provide you with a solution....hmmm.....everyone here is a volunteer, do you see now why you were asked to help us help you?

    anyways rant over......

    you have posted in a SQL2008 forum .....so here is one method for you to try..it might not be the best...and assume that you only have two rows per ID.

    SET DATEFORMAT mdy

    GO

    CREATE TABLE #mytable(

    ID INT NOT NULL

    ,ArrivalDateTime DATETIME NOT NULL

    );

    INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10000683,'8/20/16 22:25');

    INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10000683,'8/21/16 11:16');

    INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10000989,'7/11/16 11:08');

    INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10000989,'7/28/16 22:32');

    INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10001033,'7/29/16 14:53');

    INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10001033,'8/14/16 11:14');

    INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10001350,'7/24/16 20:32');

    INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10001350,'8/27/16 6:49');

    INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10002866,'7/6/16 18:28');

    INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10002866,'7/9/16 17:14');

    WITH pt AS (

    SELECT Id,

    MAX(CASE WHEN rn = 1 THEN at END )AS at1,

    MAX(CASE WHEN rn =2 THEN at END) AS at2

    FROM (

    SELECT Id,

    dbo.#mytable.ArrivalDateTime AS at,

    ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ArrivalDateTime) rn

    FROM #mytable) x

    group by id)

    SELECT Id,

    at1,

    at2,

    DATEDIFF(hh, at1, at2) AS hrdiff

    FROM pt

    WHERE(DATEDIFF(hh, at1, at2) < 72);

    DROP TABLE #mytable

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

  • valeryk2000 (9/27/2016)


    I do not know what would be the better way to present a simple table with 7K+ records (Excel file). The table has 2 columns, ID and Arrival DateTime. Sorted by ID and then by Arrival DateTime. In the past I wrote a code with double cursors iterating over each ID (not for this table and not for this task). I thought that there may be a better way to do it.

    Thanks

    Now, based on the sample data, what should the results look like?

  • Now each pair where the difference between two dates is less than 72 hrs will appear in a new table or result set

    Thanks

  • Thank you. I understand and, as you can see above, provided an sql version of the table. And thank you for your code - I'll test it tomorrow (now out of the network)

  • Here are two solutions... The 1st will work in 2008 & 2008R2. The 2nd will work with 2012 and above...

    Note: The full script, including the creation & population of test data is attached as well.

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

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

    -- 2008 & 2008R2 version --

    IF OBJECT_ID('tempdb..#temp', 'U') IS NOT NULL

    DROP TABLE #temp;

    SELECT

    a.ID,

    a.ArrivalDateTime,

    rn = ROW_NUMBER() OVER (PARTITION BY a.ID ORDER BY a.ArrivalDateTime)

    INTO #temp

    FROM

    #Appointments a;

    SELECT

    t1.ID,

    PrevDate = t2.ArrivalDateTime,

    t1.ArrivalDateTime,

    TimeSincePrevDate = DATEDIFF(hh, t2.ArrivalDateTime, t1.ArrivalDateTime)

    FROM

    #temp t1

    JOIN #temp t2

    ON t1.ID = t2.ID

    AND t1.rn = t2.rn + 1

    WHERE

    DATEDIFF(hh, t2.ArrivalDateTime, t1.ArrivalDateTime) < 72

    ORDER BY

    t1.id,

    t1.ArrivalDateTime;

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

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

    -- 2012 & up version --

    WITH

    cte_GetPrev AS (

    SELECT

    a.ID,

    PrevDate = LAG(a.ArrivalDateTime, 1) OVER (PARTITION BY a.ID ORDER BY a.ArrivalDateTime),

    a.ArrivalDateTime,

    TimeSincePrevDate = DATEDIFF(hh, LAG(a.ArrivalDateTime, 1) OVER (PARTITION BY a.ID ORDER BY a.ArrivalDateTime), a.ArrivalDateTime)

    FROM

    #Appointments a

    )

    SELECT

    gp.ID,

    gp.PrevDate,

    gp.ArrivalDateTime,

    gp.TimeSincePrevDate

    FROM

    cte_GetPrev gp

    WHERE

    gp.TimeSincePrevDate < 72;

  • Thank you. However the task may be more complicated: in some cases there is more than 2 IDs (check 10011580, 10014918, just for example, in my table posted @ 10:05:34)

  • valeryk2000 (9/27/2016)


    Thank you. However the task may be more complicated: in some cases there is more than 2 IDs (check 10011580, 10014918, just for example, in my table posted @ 10:05:34)

    I just assumed that was just bad data and filtered it out by adding "WITH (IGNORE_DUP_KEY = ON)"...

    Aside from violating the laws of physics (it's impossible to "arrive" twice at the exact same time), it doesn't complicate anything for either of the two solutions. just remove the PK constraint and rerun it... 10501108, 11320410 & 10042522 (neither 10011580 nor 10014918 had dupe dates in your sample data :-P) will show up having TimeSincePrevDate = 0.

  • valeryk2000 (9/27/2016)


    This is the fragment of the table

    CREATE TABLE [dbo].[Readmission_Table](

    [ID] [float] NULL,

    [ArrivalDateTime] [datetime] NULL

    ) ON [PRIMARY]

    GO

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10000683, CAST(0x0000A66801716A50 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10000683, CAST(0x0000A66900B9AB40 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10000989, CAST(0x0000A64000B778C0 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10000989, CAST(0x0000A65101735680 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10001033, CAST(0x0000A65200F54510 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10001033, CAST(0x0000A66200B91EA0 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10001350, CAST(0x0000A64D01526100 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10001350, CAST(0x0000A66F007055D0 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10002866, CAST(0x0000A63B01305240 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10002866, CAST(0x0000A63E014D6F60 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10003932, CAST(0x0000A65800C27540 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10003932, CAST(0x0000A66201422C90 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10004218, CAST(0x0000A665013357B0 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10004218, CAST(0x0000A66A00EC34C0 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10004220, CAST(0x0000A662006D5060 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10004220, CAST(0x0000A66E0072CEA0 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10005862, CAST(0x0000A66000A06680 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10005862, CAST(0x0000A662009EC0A0 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10006299, CAST(0x0000A64000CC5880 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10006299, CAST(0x0000A645014272E0 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10007852, CAST(0x0000A64F00563E20 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10007852, CAST(0x0000A65600BE1040 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10008474, CAST(0x0000A65500163F50 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10008474, CAST(0x0000A66F00503340 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10009346, CAST(0x0000A6470130DEE0 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10009346, CAST(0x0000A64E009A5BA0 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10009346, CAST(0x0000A651017BDA30 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10009903, CAST(0x0000A64E0123AFE0 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10009903, CAST(0x0000A659015752A0 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10009903, CAST(0x0000A65A0008CA00 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10010401, CAST(0x0000A64E00549840 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10010401, CAST(0x0000A65600305700 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10011580, CAST(0x0000A63E015798F0 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10011580, CAST(0x0000A6570126B550 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10011580, CAST(0x0000A65D0105BFD0 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10012712, CAST(0x0000A64E00AB1CB0 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10012712, CAST(0x0000A65D00041EB0 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10012887, CAST(0x0000A64001586BE0 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10012887, CAST(0x0000A66901236990 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10014218, CAST(0x0000A63A00C6DA40 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10014218, CAST(0x0000A64B00B80560 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10014918, CAST(0x0000A63D01731030 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10014918, CAST(0x0000A64000C9DFB0 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10014918, CAST(0x0000A6490123F630 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10015000, CAST(0x0000A63D00CAF8F0 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10015000, CAST(0x0000A65701388FA0 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10015401, CAST(0x0000A665015BB7A0 AS DateTime))

    INSERT [dbo].[Readmission_Table] ([ID], [ArrivalDateTime]) VALUES (10015401, CAST(0x0000A669015D1730 AS DateTime))

    Since this is SQL 2008, you will need a triangular join to access the PREVIOUS/NEXT rows of data.

    WITH cteWindow AS (

    SELECT

    m.ID, m.ArrivalDateTime

    , rn = ROW_NUMBER() OVER (PARTITION BY m.ID ORDER BY m.ArrivalDateTime)

    , EndOf72HrWindow = DATEADD(HOUR, 72, m.ArrivalDateTime)

    FROM [dbo].[Readmission_Table] AS m

    )

    , cteIsInWindow AS (

    SELECT curr.ID, curr.ArrivalDateTime

    , IsInPrev72HrWindow = CASE WHEN curr.ArrivalDateTime < prv.EndOf72HrWindow THEN 1 ELSE 0 END

    , IsInNext72HrWindow = CASE WHEN curr.EndOf72HrWindow > nxt.ArrivalDateTime THEN 1 ELSE 0 END

    , PrevArrivalDateTime = prv.ArrivalDateTime

    , NextArrivalDateTime = nxt.ArrivalDateTime

    FROM cteWindow AS curr

    LEFT JOIN cteWindow AS prv

    ON curr.ID = prv.ID

    AND curr.rn = prv.rn +1

    LEFT JOIN cteWindow AS nxt

    ON curr.ID = nxt.ID

    AND curr.rn = nxt.rn -1

    )

    SELECT cte.ID, cte.ArrivalDateTime

    FROM cteIsInWindow AS cte

    WHERE IsInPrev72HrWindow = 1

    OR IsInNext72HrWindow = 1

    ORDER BY cte.ID, cte.ArrivalDateTime;

  • Oh, gosh, so much SQL...

    So complicated code is not needed at all, if we think about the task.

    We need to find which admissions have following up re-admissions of the same patient within 72 hours from the time of the admission.

    OK.

    Let's do it step by step.

    1. We need to find out what is the 72 hors time span for each admission recordedin the table:

    select A1.[ID], A1.[ArrivalDateTime], DATEADD(hh, 72, A1.[ArrivalDateTime]) TimeSpan

    from [dbo].[Readmission_Table] A1

    Done.

    2. We need to find every other admissions for the same patient (same ID) with ArrivalDateTime within that time span.

    I guess a simple INNER JOIN would do:

    select A1.[ID], A1.[ArrivalDateTime], AN.ArrivalDateTime NextAdmissionDatetime

    from [dbo].[Readmission_Table] A1

    INNER JOIN [dbo].[Readmission_Table] AN ON A1.ID = AN.ID

    and AN.ArrivalDateTime > A1.ArrivalDateTime

    and AN.ArrivalDateTime <= DATEADD(hh, 72, A1.[ArrivalDateTime])

    Order by 1,2,3

    3. Hold on... We're done!

    Or, probably, you want to see only 1st of readmissions, not all which fall within 72 hours.

    In such case we need MIN of NextAdmissionDatetime:

    select A1.[ID], A1.[ArrivalDateTime], MIN(AN.ArrivalDateTime) FirstReAdmissionWithin72hours

    from [dbo].[Readmission_Table] A1

    INNER JOIN [dbo].[Readmission_Table] AN ON A1.ID = AN.ID

    and AN.ArrivalDateTime > A1.ArrivalDateTime and AN.ArrivalDateTime <= DATEADD(hh, 72, A1.[ArrivalDateTime])

    group by A1.[ID], A1.[ArrivalDateTime]

    order by 1,2

    Easy, yeah?:hehe:

    _____________
    Code for TallyGenerator

  • Viewing 15 posts - 1 through 15 (of 52 total)

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