英文:
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,'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')
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 >= 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;
答案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 <= 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 <= 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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论