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

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

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

问题

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

以下是表格和示例数据：

  1. create table #MemberTable
  2. (memberId int
  3. ,benefitId int
  4. ,startDate datetime
  5. ,endDate dateTime)
  7. insert #MemberTable
  8. values(1,1,'2020-01-15','2022-01-15'),
  9. (1,2,'2019-05-20','2020-10-15'),
  10. (2,1,'2022-12-06','2024-01-20'),
  11. (2,2,'2020-01-05','2020-11-06'),
  12. (1,3,'2021-06-15','2022-07-01'),
  13. (3,3,'2020-02-28','2022-02-28'),
  14. (3,2,'2020-01-15','2020-12-15')

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

  1. /*
  2. YEAR_MONTH	benefitId	active_b_count
  3. 201905			2			1
  4. 201906			2			1
  5. 201907			2			1
  6. 201908			2			1
  7. 201909			2			1
  8. 201910			2			1
  9. 201911			2			1
  10. 201912			2			1
  11. 202001			2			3
  12. 202002			2			3
  13. 202003			2			3
  14. 202004			2			3
  15. 202005			2			3
  16. 202006			2			3
  17. 202007			2			3
  18. 202008			2			3
  19. 202009			2			3
  20. 202010			2			3
  21. 202011			2			2
  22. 202012			2			1
  23. */

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

  1. WITH YearMonths AS (
  2.     SELECT DISTINCT
  3.         YEAR(startDate) AS year,
  4.         MONTH(startDate) AS month
  5.     FROM #MemberTable
  6.     UNION
  7.     SELECT DISTINCT
  8.         YEAR(endDate) AS year,
  9.         MONTH(endDate) AS month
  10.     FROM #MemberTable
  11. ),
  12. AllYearMonths AS (
  13.     SELECT DISTINCT year, month
  14.     FROM YearMonths
  15. ),
  16. BenefitCounts AS (
  17.     SELECT
  18.         ym.year * 100 + ym.month AS YEAR_MONTH,
  19.         m.benefitId,
  20.         count(m.benefitId) AS active_b_count
  21.     FROM AllYearMonths ym
  22.     CROSS JOIN (SELECT DISTINCT benefitId FROM #MemberTable) m
  23.     LEFT JOIN #MemberTable t ON m.benefitId = t.benefitId
  24.                              AND (ym.year * 100 + ym.month >= YEAR(t.startDate) * 100 + MONTH(t.startDate))
  25.                              AND (ym.year * 100 + ym.month <= YEAR(t.endDate) * 100 + MONTH(t.endDate))
  26.     WHERE ym.year * 100 + ym.month >= (SELECT MIN(YEAR(startDate) * 100 + MONTH(startDate)) FROM #MemberTable)
  27.       AND ym.year * 100 + ym.month <= (SELECT MAX(YEAR(endDate) * 100 + MONTH(endDate)) FROM #MemberTable)
  28.     GROUP BY ym.year, ym.month, m.benefitId
  29. )
  30. SELECT
  31.     bc.YEAR_MONTH,
  32.     bc.benefitId,
  33.     bc.active_b_count
  34. FROM BenefitCounts bc where bc.benefitId = 2
  35. 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

  1. create table #MemberTable
  2. (memberId int
  3. ,benefitId int
  4. ,startDate datetime
  5. ,endDate dateTime)
  7. insert #MemberTable
  8. values(1,1,&#39;2020-01-15&#39;,&#39;2022-01-15&#39;),
  9. (1,2,&#39;2019-05-20&#39;,&#39;2020-10-15&#39;),
  10. (2,1,&#39;2022-12-06&#39;,&#39;2024-01-20&#39;),
  11. (2,2,&#39;2020-01-05&#39;,&#39;2020-11-06&#39;),
  12. (1,3,&#39;2021-06-15&#39;,&#39;2022-07-01&#39;),
  13. (3,3,&#39;2020-02-28&#39;,&#39;2022-02-28&#39;),
  14. (3,2,&#39;2020-01-15&#39;,&#39;2020-12-15&#39;)

Below is the result set we want for benefit Id 2

  1. /*
  2. YEAR_MONTH	benefitId	active_b_count
  3. 201905			2			1
  4. 201906			2			1
  5. 201907			2			1
  6. 201908			2			1
  7. 201909			2			1
  8. 201910			2			1
  9. 201911			2			1
  10. 201912			2			1
  11. 202001			2			3
  12. 202002			2			3
  13. 202003			2			3
  14. 202004			2			3
  15. 202005			2			3
  16. 202006			2			3
  17. 202007			2			3
  18. 202008			2			3
  19. 202009			2			3
  20. 202010			2			3
  21. 202011			2			2
  22. 202012			2			1
  24. */

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

  1. WITH YearMonths AS (
  2.     SELECT DISTINCT
  3.         YEAR(startDate) AS year,
  4.         MONTH(startDate) AS month
  5.     FROM #MemberTable
  6.     UNION
  7.     SELECT DISTINCT
  8.         YEAR(endDate) AS year,
  9.         MONTH(endDate) AS month
  10.     FROM #MemberTable
  11. ),
  12. AllYearMonths AS (
  13.     SELECT DISTINCT year, month
  14.     FROM YearMonths
  15. ),
  16. BenefitCounts AS (
  17.     SELECT
  18.         ym.year * 100 + ym.month AS YEAR_MONTH,
  19.         m.benefitId,
  20.         count(m.benefitId) AS active_b_count
  21.     FROM AllYearMonths ym
  22.     CROSS JOIN (SELECT DISTINCT benefitId FROM #MemberTable) m
  23.     LEFT JOIN #MemberTable t ON m.benefitId = t.benefitId
  24.                              AND (ym.year * 100 + ym.month &gt;= YEAR(t.startDate) * 100 + MONTH(t.startDate))
  25.                              AND (ym.year * 100 + ym.month &lt;= YEAR(t.endDate) * 100 + MONTH(t.endDate))
  26.     WHERE ym.year * 100 + ym.month &gt;= (SELECT MIN(YEAR(startDate) * 100 + MONTH(startDate)) FROM #MemberTable)
  27.       AND ym.year * 100 + ym.month &lt;= (SELECT MAX(YEAR(endDate) * 100 + MONTH(endDate)) FROM #MemberTable)
  28.     GROUP BY ym.year, ym.month, m.benefitId
  29. )
  30. SELECT
  31.     bc.YEAR_MONTH,
  32.     bc.benefitId,
  33.     bc.active_b_count
  34. FROM BenefitCounts bc where bc.benefitId = 2
  35. ORDER BY bc.YEAR_MONTH;

答案1

得分: 2

以下是翻译好的内容：

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

  1. WITH T10 AS
  2. (
  3. SELECT n
  4. FROM  (VALUES(0), (0),
  5.              (0), (0),
  6.              (0), (0),
  7.              (0), (0),
  8.              (0), (0))n(n) 
  10. ), 
  11. Nums(num) AS
  12. (
  13. SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) - 1
  14. FROM   T10 a, T10 b, T10 c 
  15. ),
  16. Source AS
  17. (
  18. SELECT memberId,
  19.        benefitId,
  20.        DATEFROMPARTS(YEAR(startDate), MONTH(startDate), 1) AS startMonth,
  21.        DATEFROMPARTS(YEAR(endDate), MONTH(endDate), 1)     AS endMonth
  22. FROM   #MemberTable
  23. ), MinMax As
  24. (
  25. SELECT MIN(startMonth) AS MinMonth,
  26.        MAX(endMonth)   AS MaxMonth,
  27.        benefitId
  28. FROM   Source 
  29. GROUP BY benefitId
  30. ), Months AS
  31. (
  32. SELECT DATEADD(MONTH, num, MinMonth) AS mnth,
  33.        benefitId
  34. FROM   MinMax
  35.        JOIN Nums
  36.          ON Nums.num &lt;= DATEDIFF(MONTH, MinMonth, MaxMonth) 
  38. ), Grouped AS
  39. (
  40. SELECT date      AS mnth,
  41.        SUM(diff) AS diff,
  42.        benefitId
  43. FROM   Source
  44.        CROSS apply (VALUES(startMonth, 1),
  45.                           (DATEADD(MONTH,1,endMonth), -1)) v(date, diff )
  46. GROUP  BY date , benefitId
  48. )
  49. SELECT    M.mnth,
  50.           M.benefitId,
  51.           SUM(G.diff) OVER (PARTITION BY M.benefitId ORDER BY M.mnth ROWS UNBOUNDED PRECEDING)
  52. FROM      Months M    
  53. LEFT JOIN Grouped G
  54. 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.

  1. WITH T10 AS
  2. (
  3. SELECT n
  4. FROM  (VALUES(0), (0),
  5.              (0), (0),
  6.              (0), (0),
  7.              (0), (0),
  8.              (0), (0))n(n) 
  10. ), 
  11. Nums(num) AS
  12. (
  13. SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) - 1
  14. FROM   T10 a, T10 b, T10 c 
  15. ),
  16. Source AS
  17. (
  18. SELECT memberId,
  19.        benefitId,
  20.        DATEFROMPARTS(YEAR(startDate), MONTH(startDate), 1) AS startMonth,
  21.        DATEFROMPARTS(YEAR(endDate), MONTH(endDate), 1)     AS endMonth
  22. FROM   #MemberTable
  23. ), MinMax As
  24. (
  25. SELECT MIN(startMonth) AS MinMonth,
  26.        MAX(endMonth)   AS MaxMonth,
  27.        benefitId
  28. FROM   Source 
  29. GROUP BY benefitId
  30. ), Months AS
  31. (
  32. SELECT DATEADD(MONTH, num, MinMonth) AS mnth,
  33.        benefitId
  34. FROM   MinMax
  35.        JOIN Nums
  36.          ON Nums.num &lt;= DATEDIFF(MONTH, MinMonth, MaxMonth) 
  38. ), Grouped AS
  39. (
  40. SELECT date      AS mnth,
  41.        SUM(diff) AS diff,
  42.        benefitId
  43. FROM   Source
  44.        CROSS apply (VALUES(startMonth, 1),
  45.                           (DATEADD(MONTH,1,endMonth), -1)) v(date, diff )
  46. GROUP  BY date , benefitId
  48. )
  49. SELECT    M.mnth,
  50.           M.benefitId,
  51.           SUM(G.diff) OVER (PARTITION BY M.benefitId ORDER BY M.mnth ROWS UNBOUNDED PRECEDING)
  52. FROM      Months M    
  53. LEFT JOIN Grouped G
  54. ON        M.mnth = G.mnth AND M.benefitId = G.benefitId

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

发表评论

匿名网友

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

确定