我正在尝试从下面的列表中获取居民人数。如果一个居民被列出一次以上，并且“结束日期”（记录1）比“开始日期”（记录2）短一天，那么该居民应仅被计数一次。
因此，在下面的示例中，BEAL应该计数一次，CARVER应该计数两次，CLAUS应该计数三次。
所需的输出总驻地计数= 13。
不知道如何实现这一目标。
facility Resident StartDate Enddate Payer
ABC AMMONS 9/12/2020 11/4/2020 Medicaid
ABC ANDERSON 3/4/2020 11/18/2020 Medicaid
ABC BEAL 1/1/2020 11/21/2020 Medicaid
ABC BEAL 11/22/2020 11/24/2020 Medicaid
ABC BEESON 10/26/2020 11/11/2020 Medicaid
ABC BOLING 2/29/2020 11/20/2020 Medicaid
ABC Boaring 5/13/2019 11/23/2020 Medicaid
ABC BROCATO 11/12/2020 11/27/2020 Medicaid
ABC BUSCH 3/9/2019 11/4/2020 Medicaid
ABC CARVER 8/6/2020 11/1/2020 Medicaid
ABC CARVER 11/6/2020 11/13/2020 Medicaid
ABC CLAUS 5/7/2020 11/1/2020 Medicaid
ABC CLAUS 11/5/2020 11/11/2020 Medicaid
ABC CLAUS 11/12/2020 11/30/2020 Medicaid
ABC CLAUS 12/15/2020 12/25/2020 Medicaid
试试这个
; WITH cte AS (
SELECT Resident, StartDate, EndDate
, LAG( EndDate, 1, NULL ) OVER (PARTITION BY Resident ORDER BY StartDate) LastEndDate
FROM tmp
), cte1 AS (
SELECT Resident, StartDate, EndDate, LastEndDate
, ResidentCount = CASE WHEN StartDate <= DATEADD(day, 1, LastEndDate) THEN 0 ELSE 1 END
FROM cte
)
SELECT "Total Resident Count" = SUM(ResidentCount)
FROM cte1
查询测试
WITH tmp AS (
SELECT Resident, StartDate = CONVERT(Date, StartDate) , EndDate = CONVERT(DATE, EndDate)
FROM (
VALUES
('AMMONS','9/12/2020','11/4/2020')
, ('ANDERSON','3/4/2020','11/18/2020')
, ('BEAL','1/1/2020','11/21/2020')
, ('BEAL','11/22/2020','11/24/2020')
, ('BEESON','10/26/2020','11/11/2020')
, ('BOLING','2/29/2020','11/20/2020')
, ('Boaring','5/13/2019','11/23/2020')
, ('BROCATO','11/12/2020','11/27/2020')
, ('BUSCH','3/9/2019','11/4/2020')
, ('CARVER','8/6/2020','11/1/2020')
, ('CARVER','11/6/2020','11/13/2020')
, ('CLAUS','5/7/2020','11/1/2020')
, ('CLAUS','11/5/2020','11/11/2020')
, ('CLAUS','11/12/2020','11/30/2020')
, ('CLAUS','12/15/2020','12/25/2020')
) a (Resident, StartDate, EndDate)
), cte AS (
SELECT Resident, StartDate, EndDate
, LAG( EndDate, 1, NULL ) OVER (PARTITION BY Resident ORDER BY StartDate) LastEndDate
FROM tmp
), cte1 AS (
SELECT Resident, StartDate, EndDate, LastEndDate
, ResidentCount = CASE WHEN StartDate <= DATEADD(day, 1, LastEndDate) THEN 0 ELSE 1 END
FROM cte
)
SELECT "Total Resident Count" = SUM(ResidentCount)
FROM cte1
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句