根据两个日期字段在 2018 年 1 月 1 日 -2020 年 12 月 31 日之间执行每月不同受益人计数

发布于 2025-01-15 21:20:15 字数 355 浏览 0 评论 0原文

使用 Microsoft SQL Server 在 2018 年 1 月 1 日至 2020 年 12 月 31 日之间执行每月不同受益人计数。

下面是我的代码，但我必须每个月更改它，有什么方法可以使用 2 个不同的日期字段对 2018 年到 2020 年的每个月进行分组？

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'

原文

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'

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

轻许诺言 2025-01-22 21:20:15

对同一个月的日期进行分组的一种简单方法是

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

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

回复收藏 0 原文

夜深人未静 2025-01-22 21:20:15

一种解决方案是使用递归 CTE。您从锚查询开始，并在递归部分中使用 DATEADD 函数将其与其自身联合。即使计数为 0，此解决方案也会为您提供每个月的信息，而不是仅对数据进行分组，这将忽略任何不存在的月份。

像这样的东西：

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

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

回复收藏 0 原文

~没有更多了~