返回按月份查询的会员数量,包括会员的开始日期和结束日期。

huangapple go评论77阅读模式
英文:

SQL Query to return member count with a membership start date and end date by Month

问题

我们想要按月份统计拥有某种福利的会员数量。如果会员和福利的结束日期是上个月,那么活跃福利计数(active_b_count)会减少1;同样地,如果会员和福利的开始日期是本月,活跃福利计数会增加1。year_month列应该是连续的,从最早的开始日期月份和年份开始,以最后的结束日期月份和年份结束。

以下是表格和示例数据:

create table #MemberTable
(memberId int
,benefitId int
,startDate datetime
,endDate dateTime)

insert #MemberTable
values(1,1,'2020-01-15','2022-01-15'),
(1,2,'2019-05-20','2020-10-15'),
(2,1,'2022-12-06','2024-01-20'),
(2,2,'2020-01-05','2020-11-06'),
(1,3,'2021-06-15','2022-07-01'),
(3,3,'2020-02-28','2022-02-28'),
(3,2,'2020-01-15','2020-12-15')

以下是我们想要的benefit Id为2的结果集:

/*
YEAR_MONTH	benefitId	active_b_count
201905			2			1
201906			2			1
201907			2			1
201908			2			1
201909			2			1
201910			2			1
201911			2			1
201912			2			1
202001			2			3
202002			2			3
202003			2			3
202004			2			3
202005			2			3
202006			2			3
202007			2			3
202008			2			3
202009			2			3
202010			2			3
202011			2			2
202012			2			1
*/

这是我的尝试,但是如果没有活动发生,我很难让月份连续,并且似乎一直到2024年,我都得到了1个活跃计数,而我们知道benefit id 2的最新结束日期是2020年12月15日:

WITH YearMonths AS (
    SELECT DISTINCT
        YEAR(startDate) AS year,
        MONTH(startDate) AS month
    FROM #MemberTable
    UNION
    SELECT DISTINCT
        YEAR(endDate) AS year,
        MONTH(endDate) AS month
    FROM #MemberTable
),
AllYearMonths AS (
    SELECT DISTINCT year, month
    FROM YearMonths
),
BenefitCounts AS (
    SELECT
        ym.year * 100 + ym.month AS YEAR_MONTH,
        m.benefitId,
        count(m.benefitId) AS active_b_count
    FROM AllYearMonths ym
    CROSS JOIN (SELECT DISTINCT benefitId FROM #MemberTable) m
    LEFT JOIN #MemberTable t ON m.benefitId = t.benefitId
                             AND (ym.year * 100 + ym.month >= YEAR(t.startDate) * 100 + MONTH(t.startDate))
                             AND (ym.year * 100 + ym.month <= YEAR(t.endDate) * 100 + MONTH(t.endDate))
    WHERE ym.year * 100 + ym.month >= (SELECT MIN(YEAR(startDate) * 100 + MONTH(startDate)) FROM #MemberTable)
      AND ym.year * 100 + ym.month <= (SELECT MAX(YEAR(endDate) * 100 + MONTH(endDate)) FROM #MemberTable)
    GROUP BY ym.year, ym.month, m.benefitId
)
SELECT
    bc.YEAR_MONTH,
    bc.benefitId,
    bc.active_b_count
FROM BenefitCounts bc where bc.benefitId = 2
ORDER BY bc.YEAR_MONTH;
英文:

We would like to get a monthly count of the number of members who have a type of benefit by Month. If the end date for a member and benefit is last month then the active_b_count would decreased by 1 and similarly if the start date for the member and benefit is this month
then the active_b_count would increase by one. The year_month column should be consecutive and start with the earliest start date month and year and should end with the last end date month and year
Below is table and example data

create table #MemberTable
(memberId int
,benefitId int
,startDate datetime
,endDate dateTime)

insert #MemberTable
values(1,1,&#39;2020-01-15&#39;,&#39;2022-01-15&#39;),
(1,2,&#39;2019-05-20&#39;,&#39;2020-10-15&#39;),
(2,1,&#39;2022-12-06&#39;,&#39;2024-01-20&#39;),
(2,2,&#39;2020-01-05&#39;,&#39;2020-11-06&#39;),
(1,3,&#39;2021-06-15&#39;,&#39;2022-07-01&#39;),
(3,3,&#39;2020-02-28&#39;,&#39;2022-02-28&#39;),
(3,2,&#39;2020-01-15&#39;,&#39;2020-12-15&#39;)

Below is the result set we want for benefit Id 2

/*
YEAR_MONTH	benefitId	active_b_count
201905			2			1
201906			2			1
201907			2			1
201908			2			1
201909			2			1
201910			2			1
201911			2			1
201912			2			1
202001			2			3
202002			2			3
202003			2			3
202004			2			3
202005			2			3
202006			2			3
202007			2			3
202008			2			3
202009			2			3
202010			2			3
202011			2			2
202012			2			1

*/

This was my attempt but I am struggling to get the months consecutive if no activity has taken place and I seem to get 1 active count all the way to 2024 when we know the latest end date for benefit id 2 was December 15th 2020

WITH YearMonths AS (
    SELECT DISTINCT
        YEAR(startDate) AS year,
        MONTH(startDate) AS month
    FROM #MemberTable
    UNION
    SELECT DISTINCT
        YEAR(endDate) AS year,
        MONTH(endDate) AS month
    FROM #MemberTable
),
AllYearMonths AS (
    SELECT DISTINCT year, month
    FROM YearMonths
),
BenefitCounts AS (
    SELECT
        ym.year * 100 + ym.month AS YEAR_MONTH,
        m.benefitId,
        count(m.benefitId) AS active_b_count
    FROM AllYearMonths ym
    CROSS JOIN (SELECT DISTINCT benefitId FROM #MemberTable) m
    LEFT JOIN #MemberTable t ON m.benefitId = t.benefitId
                             AND (ym.year * 100 + ym.month &gt;= YEAR(t.startDate) * 100 + MONTH(t.startDate))
                             AND (ym.year * 100 + ym.month &lt;= YEAR(t.endDate) * 100 + MONTH(t.endDate))
    WHERE ym.year * 100 + ym.month &gt;= (SELECT MIN(YEAR(startDate) * 100 + MONTH(startDate)) FROM #MemberTable)
      AND ym.year * 100 + ym.month &lt;= (SELECT MAX(YEAR(endDate) * 100 + MONTH(endDate)) FROM #MemberTable)
    GROUP BY ym.year, ym.month, m.benefitId
)
SELECT
    bc.YEAR_MONTH,
    bc.benefitId,
    bc.active_b_count
FROM BenefitCounts bc where bc.benefitId = 2
ORDER BY bc.YEAR_MONTH;

答案1

得分: 2

以下是翻译好的内容:

这里有一种方法DB Fiddle - 你需要稍微调整一下以获得你想要的YEAR_MONTH,但基本上可以满足你的需求。

WITH T10 AS
(
SELECT n
FROM  (VALUES(0), (0),
             (0), (0),
             (0), (0),
             (0), (0),
             (0), (0))n(n) 

), 
Nums(num) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) - 1
FROM   T10 a, T10 b, T10 c 
),
Source AS
(
SELECT memberId,
       benefitId,
       DATEFROMPARTS(YEAR(startDate), MONTH(startDate), 1) AS startMonth,
       DATEFROMPARTS(YEAR(endDate), MONTH(endDate), 1)     AS endMonth
FROM   #MemberTable
), MinMax As
(
SELECT MIN(startMonth) AS MinMonth,
       MAX(endMonth)   AS MaxMonth,
       benefitId
FROM   Source 
GROUP BY benefitId
), Months AS
(
SELECT DATEADD(MONTH, num, MinMonth) AS mnth,
       benefitId
FROM   MinMax
       JOIN Nums
         ON Nums.num &lt;= DATEDIFF(MONTH, MinMonth, MaxMonth) 

), Grouped AS
(
SELECT date      AS mnth,
       SUM(diff) AS diff,
       benefitId
FROM   Source
       CROSS apply (VALUES(startMonth, 1),
                          (DATEADD(MONTH,1,endMonth), -1)) v(date, diff )
GROUP  BY date , benefitId

)
SELECT    M.mnth,
          M.benefitId,
          SUM(G.diff) OVER (PARTITION BY M.benefitId ORDER BY M.mnth ROWS UNBOUNDED PRECEDING)
FROM      Months M    
LEFT JOIN Grouped G
ON        M.mnth = G.mnth AND M.benefitId = G.benefitId


英文:

Here's one way DB Fiddle - you need to tweak it slightly to get your desired YEAR_MONTH but basically does what you need.

WITH T10 AS
(
SELECT n
FROM  (VALUES(0), (0),
             (0), (0),
             (0), (0),
             (0), (0),
             (0), (0))n(n) 

), 
Nums(num) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) - 1
FROM   T10 a, T10 b, T10 c 
),
Source AS
(
SELECT memberId,
       benefitId,
       DATEFROMPARTS(YEAR(startDate), MONTH(startDate), 1) AS startMonth,
       DATEFROMPARTS(YEAR(endDate), MONTH(endDate), 1)     AS endMonth
FROM   #MemberTable
), MinMax As
(
SELECT MIN(startMonth) AS MinMonth,
       MAX(endMonth)   AS MaxMonth,
       benefitId
FROM   Source 
GROUP BY benefitId
), Months AS
(
SELECT DATEADD(MONTH, num, MinMonth) AS mnth,
       benefitId
FROM   MinMax
       JOIN Nums
         ON Nums.num &lt;= DATEDIFF(MONTH, MinMonth, MaxMonth) 

), Grouped AS
(
SELECT date      AS mnth,
       SUM(diff) AS diff,
       benefitId
FROM   Source
       CROSS apply (VALUES(startMonth, 1),
                          (DATEADD(MONTH,1,endMonth), -1)) v(date, diff )
GROUP  BY date , benefitId

)
SELECT    M.mnth,
          M.benefitId,
          SUM(G.diff) OVER (PARTITION BY M.benefitId ORDER BY M.mnth ROWS UNBOUNDED PRECEDING)
FROM      Months M    
LEFT JOIN Grouped G
ON        M.mnth = G.mnth AND M.benefitId = G.benefitId


huangapple
  • 本文由 发表于 2023年8月9日 00:40:10
  • 转载请务必保留本文链接:https://go.coder-hub.com/76861592.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定