Calculate time difference between two related visitors without using cursor

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

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

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)

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.

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: