Sergiy (9/29/2016)

---

valeryk2000 (9/29/2016)

---

Testing update:

J Livingston's code runs OK - same results as in Sergiy's.

Thanks a lot!

Actually, our codes return quite different results.

Probably not on the set you're looking at, but nevertheless.

Here is the script where you can see several cases when those scripts act differently:

CREATE TABLE #mytable(

ID INT NOT NULL

,ArrivalDateTime DATETIME NOT NULL

);

-- same time

INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10001000,'20160820 22:25');

INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10001000,'20160820 22:25');

-- exactly 72 hours between admissions

INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10001072,'20160728 11:08');

INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10001072,'20160731 11:08');

-- more than 1 readmission within 72 hours

INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10001003,'20160729 14:53');

INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10001003,'20160730 11:14');

INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10001003,'20160731 16:21');

-- no < 72 hours readmission for 1st appointment, but there is one for a second one

INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10001023,'20160729 14:53');

INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10001023,'20160822 15:12');

INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10001023,'20160824 09:15');

-- 2 pairs of readmissions - <72 hours within each pair, > 72 hours between pairs

INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10001202,'20160729 14:53');

INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10001202,'20160730 11:14');

INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10001202,'20160819 08:21');

INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10001202,'20160821 09:34');

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

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

from [dbo].#mytable A1

INNER JOIN [dbo].#mytable AN ON A1.ID = AN.ID

and AN.ArrivalDateTime > A1.ArrivalDateTime

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

Order by 1,2,3

DROP TABLE #mytable

I can't tell which is right and which is wrong.

You'll have to verify the outcome of both scripts against the business rules in place.

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