主页 / dba / 问题 / 270142
Accepted
Juan Velez
Asked: 2020-07-01 07:43:21 +0800 CST

从 SQL Server 表中的开始日期和结束日期派生日期

  • 772

我正在使用 SQL Server 2016

我有一个表，其中每月包含 1 行，将患者分配给特定的提供者。

一年中可以将一名患者分配给多个提供者。

如何派生日期跨度（开始日期和结束日期）来表示将患者分配给每个提供者的时间。

我的表如下所示：

+----------+---------------+------------+-----------+
| Provider | Patient       | StartDate  | EndDate  | 
+----------+---------------+------------+-----------+
| 1922157  | 12345         | 20191201  | 20191231 | 
| 1904176  | 12345         | 20191101  | 20191201 |
| 1904176  | 12345         | 20191001  | 20191101 |
| 1904176  | 12345         | 20190901  | 20191001 | 
| 1904176  | 12345         | 20190801  | 20190901 |
| 1904176  | 12345         | 20190701  | 20190801 |
| 1904176  | 12345         | 20190601  | 20190701 |
| 1904176  | 12345         | 20190501  | 20190601 |
| 1904176  | 12345         | 20190401  | 20190501 |
| 1904176  | 12345         | 20190301  | 20190401 |
| 1904176  | 12345         | 20190201  | 20190301 |
| 1922157  | 12345         | 20190101  | 20190201 |
| 1922157  | 56789         | 20190101  | 20190201 |
+----------+---------------+------------+-----------+

在这种情况下，患者 12345 被分配到 2 个不同的提供者。一个用于 2 个月，1 月，然后是 12 月，另一个用于一年中的其余时间（10 个月）2 月至 11 月。患者 56789 仅分配给 1 个提供者 (1922157) 1 个月（12 月）。

我正在努力做到这一点，所以我的输出如下表所示，但我遇到了我认为的问题，因为患者在一年中的 2 个不同时间被分配到同一个 pcp。我尝试使用 lag 函数，但我只在某些情况下得到正确的结果，但并非所有情况都得到正确的结果，例如这种特殊情况。

+----------+---------------+------------+-----------+
| Provider | Patient       | StartDate  | EndDate  | 
+----------+---------------+------------+-----------+
| 1922157  | 12345         | 20190101  | 20190201  | 
| 1904176  | 12345         | 20190201  | 20191201  | 
| 1922157  | 12345         | 20191201  | 20191231  | 
| 1922157  | 56789         | 20191201  | 20191231  |
+----------+---------------+------------+-----------+

更新：正在做更多的研究，发现了以下帖子：

https://stackoverflow.com/questions/35900765/ms-sql-combine-date-rows-into-start-end-date

我只是将我的表格放入上述问题答案中的代码中，并针对我的一些案例进行了测试，看起来它可能会完成工作。不幸的是，我的基表有 140k 行日期需要计算，所以我不确定运行需要多长时间。现在已经运行了 6 分钟，我将返回结果。

sql-server date-math

1 个回答

  1. Best Answer

    我想我明白你想要做什么。您正在尝试在提供者处获取患者的开始日期和结束日期，只要期间的开始日期和结束日期之间没有间隔。我用您采样的数据创建了一个测试表。

    Create table test (Provider int, Patient int, startdate date, enddate date)
insert into test (Provider, Patient, StartDate, EndDate)

SELECT * FROM 
(SELECT 1922157 as Provider  , 12345 as Patient         , '2019-12-01' as StartDate , '2019-12-31' as EndDate
union all SELECT 1904176  , 12345         , '2019-11-01'  , '2019-12-01' 
union all SELECT 1904176  , 12345         , '2019-10-01'  , '2019-11-01' 
union all SELECT 1904176  , 12345         , '2019-09-01'  , '2019-10-01' 
union all SELECT 1904176  , 12345         , '2019-08-01'  , '2019-09-01' 
union all SELECT 1904176  , 12345         , '2019-07-01'  , '2019-08-01' 
union all SELECT 1904176  , 12345         , '2019-06-01'  , '2019-07-01' 
union all SELECT 1904176  , 12345         , '2019-05-01'  , '2019-06-01' 
union all SELECT 1904176  , 12345         , '2019-04-01'  , '2019-05-01' 
union all SELECT 1904176  , 12345         , '2019-03-01'  , '2019-04-01' 
union all SELECT 1904176  , 12345         , '2019-02-01'  , '2019-03-01' 
union all SELECT 1922157  , 12345         , '2019-01-01'  , '2019-02-01' 
union all SELECT 1922157  , 56789         , '2019-01-01'  , '2019-02-01' )t

    想法是首先对数据进行排序并尝试获取开始日期和结束日期匹配的数据，以便检测日期中的漏洞。我使用“ROW_NUMBER”函数来做到这一点。然后，我找到所有匹配的行，并为匹配的行获取第一个 StartDate 和 max EndDate，然后添加所有“单独”且不匹配的行。

    我认为它适用于您提供的数据。我没有用其他数据来测试它。递归是查找不同值的最小/最大日期的另一种选择，但在这种情况下我没有使用递归。（随意给更好的名字，我走得有点快）

    ;With RowsWithNum AS
(
SELECT Provider, Patient, StartDate, EndDate, ROW_NUMBER() OVER (ORDER BY Provider, patient, StartDate) as RowNum
FROM test
)
,BeforeAndAfterDates AS
(
SELECT a.Provider, a.Patient, a.StartDate, a.RowNum, a.EndDate, b.StartDate EndStartDate, DATEPART(DAYOFYEAR, b.StartDate)-DATEPART(DAYOFYEAR,a.EndDate) as DateDiffInDays, b.EndDate as EndEndDate, b.RowNum as EndRowNum
FROM RowsWithNum a
LEFT JOIN RowsWithNum b ON b.Provider=a.Provider and b.Patient=a.Patient and b.StartDate=a.EndDate
)
SELECT Provider, Patient, Min(StartDate) as StartDate, Max(EndEndDate) as EndDate, Min(RowNum) as RowNum
FROM BeforeAndAfterDates
WHERE DateDiffInDays=0
GROUP BY Provider, Patient
UNION
SELECT a.Provider, a.Patient, a.StartDate, a.EndDate, a.RowNum
FROM BeforeAndAfterDates a
LEFT JOIN BeforeAndAfterDates b ON b.EndEndDate=a.enddate
WHERE a.DateDiffInDays IS NULL AND b.RowNum IS NULL

    这是我的结果。

    结果

    • 0

相关问题