Perform a monthly distinct beneficiary count between 1/1/2018 -12/31/2020 based on two date fields-CodePudding

Perform a monthly distinct beneficiary count between 1/1/2018 -12/31/2020 using Microsoft SQL Server.

Below is my code but I have to change it for every month, is there any way to group by each month from 2018 to 2020 with 2 different date fields?

SELECT COUNT(distinct BEN_ID) 
FROM LDS_2017andbeyond
WHERE 
[DTE_FIRST_SVC] between  '2018-01-01'  and '2018-01-31'
AND 
[DTE_LAST_SVC]  between  '2018-01-01'  and '2018-01-31'

CodePudding user response：

One simple way to group dates on the same month is the EOMONTH function. It'll return the last day of the month for a date.

SELECT 
  FORMAT(EOMONTH([DTE_FIRST_SVC]), 'yyyy-MM') AS MONTH_FIRST_SVC
, FORMAT(EOMONTH([DTE_LAST_SVC]), 'yyyy-MM') AS MONTH_LAST_SVC  
, COUNT(DISTINCT BEN_ID) AS TOTAL_UNIQUE_BEN_ID
FROM LDS_2017andbeyond
WHERE [DTE_FIRST_SVC] BETWEEN '2018-01-01' AND '2020-12-31'
  AND [DTE_LAST_SVC]  BETWEEN '2018-01-01' AND '2020-12-31'
GROUP BY EOMONTH([DTE_FIRST_SVC]), EOMONTH([DTE_LAST_SVC])
ORDER BY MONTH_FIRST_SVC DESC, MONTH_LAST_SVC DESC

CodePudding user response：

One solution would be to use a recursive CTE. You start with an anchor query and union it to itself with a DATEADD function in the recursive portion. This solution will give you every month month, even if the count is 0, as opposed to just grouping on the data, which will omit any months that arn't present.

Something like:

WITH CTE_Date AS (
    SELECT CAST('01/01/2018' AS DATE) AS GroupMonth -- Start Date.  Set as far back as necessary.  Can use a DATEADD() to make dynamic.
    
    UNION ALL
        
    SELECT DATEADD(month, 1, GroupMonth) AS GroupMonth
    FROM CTE_Date
    WHERE DATEADD(month, 1, GroupMonth) < '12/31/2020' -- End Date.  Remove the where to go to current.
)
        
SELECT 
    COUNT(distinct BEN_ID),
    CAST(MONTH(d.GroupMonth) AS VARCHAR(2))   '-'   CAST(YEAR(d.GroupMonth) AS VARCHAR(4)) AS Dt
FROM 
    LDS_2017andbeyond lds
    LEFT OUTER JOIN CTE_Date d ON 
    MONTH(lds.[DTE_FIRST_SVC]) = MONTH(d.GroupMonth) 
    AND 
    YEAR(lds.[DTE_LAST_SVC]) = YEAR(d.GroupMonth)
GROUP BY
    CAST(MONTH(d.GroupMonth) AS VARCHAR(2))   '-'   CAST(YEAR(d.GroupMonth) AS VARCHAR(4))