SQL:差距和离岛，分组日期 [英] SQL: Gaps and Islands, Grouped dates

问题描述

我试图将日期彼此间隔3天，并根据30天之内的重新录取情况分配分数. MRN每次重新入学将获得3分.以下有关修改我的查询的任何帮助都将非常有用.

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.

示例:

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

所需的输出:我真的只需要实际的访问次数，OrganizationMrn和积分. (如果将日期分组(实际访问)，则第一个日期应在30天内用于重新接纳).

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)

下面的查询使用间隔和孤岛将日期间隔在3天内.但是，如果日期是连续的，则将开始/结束日期分组.(示例:下面的查询将[1/2、1/5、1/7/，1/8、1/9]分组为一行；日期应分为两行[1/2，1/5]和[1/7/，1/8，1/9]).

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

一旦分组的日期有单独的行，我需要在30天内为每个重新入学分配3分. (每个组织的实际访问间隔为30天).上面所需的输出部分描述了示例中的日期应如何分组.

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

这篇关于SQL:差距和离岛，分组日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！