Record Count for special criteria

debugcn Published at Dev

stackuser

I am trying to get a count of Residents from the list below. If a resident is listed more than once and if the Enddate (record 1) is a day less than StartDate (record 2), then that resident should be counted just once.

So in the Example below, BEAL should be counted once, CARVER should be counted twice, CLAUS should be counted thrice.

Desired Output The Total Resident Count = 13.

Not sure how to achieve this.

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

DVT

Try this

; 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

Query to test

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

Collected from the Internet

Please contact [email protected] to delete if infringement.