I am trying to group dates within 3 days of each other and assign points based on readmission's within 30 days. A MRN would receive 3 points per readmission. Any help on modifying my query below would be great.
Example:
CREATE TABLE #z (
ID INT IDENTITY(1,1),
OrganizationMrn INT,
VisitDate DATE,
CATEGORY VARCHAR(15) )
INSERT #z(OrganizationMrn, VisitDate, CATEGORY)
VALUES
(1, '1/2/2016','Inpatient'),
(1, '1/5/2016','Inpatient'),
(1, '1/7/2016','Inpatient'),
(1, '1/8/2016','Inpatient'),
(1, '1/9/2016','Inpatient'),
(1, '2/4/2016','Inpatient'),
(1, '6/2/2016','Inpatient'),
(1, '6/3/2016','Inpatient'),
(1, '6/5/2016','Inpatient'),
(1, '6/6/2016','Inpatient'),
(1, '6/8/2016','Inpatient'),
(1, '7/1/2016','Inpatient'),
(1, '8/1/2016','Inpatient'),
(1, '8/4/2016','Inpatient'),
(1, '8/15/2016','Inpatient'),
(1, '8/18/2016','Inpatient'),
(1, '8/28/2016','Inpatient'),
(1, '10/12/2016','Inpatient'),
(1, '10/15/2016','Inpatient'),
(1, '11/17/2016','Inpatient'),
(1, '12/20/2016','Inpatient')
Desired Output: I really only need the Actual Visits, OrganizationMrn, and Points. (When dates are grouped(Actual Visits), the first date should be used for readmission within 30 days ).
ACTUAL Visits Grouped Dates Re-admissions Points
1/2/2016 (grouped 1/2, 1/5)
1/7/2016 (grouped 1/7, 1/8, 1/9) Readmit from 1/2 (3 points)
2/4/2016 Readmit from 1/7 (3 points)
6/2/2016 (grouped 6/2, 6/3, 6/5)
6/6/2016 (grouped 6/6, 6/8) Readmit from 6/2 (3 points)
7/1/2016 Readmit from 6/6 (3 points)
8/1/2016 (grouped 8/1, 8/4)
8/15/2016 (grouped 8/15, 8/18) Readmit from 8/1 (3 points)
8/28/2016 Readmit from 8/15 (3 points)
10/12/2016 (grouped 10/12, 10/15)
11/17/2016
12/20/2016
___________________________________________ 6 total readmits (18 total points)
The query below uses gaps and islands to group days within 3 days of each other. However if the dates are consecutive, the start/ end dates are grouped.(Example: The query below groups, [1/2, 1/5, 1/7/, 1/8, 1/9] into one row; the dates should be split into two rows [1/2, 1/5] and [1/7/, 1/8, 1/9]).
Once the grouped dates have individual rows I need to assign 3 points to each readmission within 30 days. (Actual Visit per OrganizationMrn within 30 days of each other). The desired output section above describes how the dates in my example should be grouped.
;WITH StartingPoints AS (
SELECT OrganizationMrn, VisitDate, ROW_NUMBER() OVER (ORDER BY VisitDate) AS Sequence FROM #z AS A
WHERE a.category = 'Inpatient' AND NOT EXISTS (
SELECT * FROM #z AS B
WHERE B.OrganizationMrn = A.OrganizationMrn AND
B.VisitDate >= DATEADD(DAY, -4, A.VisitDate) AND
B.VisitDate < A.VisitDate AND
B.Category = 'Inpatient' ) ),
EndingPoints AS (
SELECT OrganizationMrn, VisitDate, ROW_NUMBER() OVER (ORDER BY VisitDate) AS Sequence FROM #z AS A
WHERE a.category = 'Inpatient' AND NOT EXISTS (
SELECT * FROM #z AS B
WHERE B.OrganizationMrn = A.OrganizationMrn AND
B.VisitDate <= DATEADD(DAY, 4, A.VisitDate) AND
B.VisitDate > A.VisitDate AND
B.Category = 'Inpatient' ) )
SELECT S.OrganizationMrn, S.VisitDate AS StartDate, E.VisitDate AS EndDate, CEILING((DATEDIFF(DAY, S.VisitDate, E.VisitDate) + 1) / 4.0) AS Points
FROM StartingPoints AS S
JOIN EndingPoints AS E ON (E.Sequence = S.Sequence)
ORDER BY S.OrganizationMrn DESC
This answer works for the example you provided. It might be useful if you have small tables and limited admission. (It use recursion on the dates).
WITH a AS (
SELECT
z1.VisitDate
, z1.OrganizationMrn
, (SELECT MIN(VisitDate) FROM #z WHERE VisitDate > DATEADD(day, 3, z1.VisitDate)) AS NextDay
FROM
#z z1
WHERE
CATEGORY = 'Inpatient'
), a1 AS (
SELECT
OrganizationMrn
, MIN(VisitDate) AS VisitDate
, MIN(NextDay) AS NextDay
FROM
a
GROUP BY
OrganizationMrn
), b AS (
SELECT
VisitDate
, OrganizationMrn
, NextDay
, 1 AS OrderRow
FROM
a1
UNION ALL
SELECT
a.VisitDate
, a.OrganizationMrn
, a.NextDay
, b.OrderRow +1 AS OrderRow
FROM
a
JOIN b
ON a.VisitDate = b.NextDay
), c AS (
SELECT
VisitDate
, (SELECT MAX(VisitDate) FROM b WHERE b1.VisitDate > VisitDate) AS PreviousVisitDate
FROM
b b1
)
SELECT
c1.VisitDate
, CASE
WHEN DATEDIFF(day,c1.PreviousVisitDate,c1.VisitDate) < 30 THEN PreviousVisitDate
ELSE NULL
END AS ReAdmissionFrom
, CASE
WHEN DATEDIFF(day,c1.PreviousVisitDate,c1.VisitDate) < 30 THEN 3
ELSE 0
END AS Points
FROM
c c1
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With