22,206
社区成员
发帖
与我相关
我的任务
分享
WITH /* 测试数据
a(began,[end],js,zjs) AS (
SELECT '1994-01-01','1994-12-01',100.00,1200.00 UNION ALL
SELECT '1995-01-01','1995-05-01',100.00,500.00 UNION ALL
SELECT '1995-06-01','1998-03-01',100.00,34000.00
), */
/* 准备一个年份和起至月份的辅助表,方便查询
ym (year,began,[end])AS (
SELECT 1994,'1994-01-01','1994-12-01' UNION ALL
SELECT 1995,'1995-01-01','1995-12-01' UNION ALL
SELECT 1996,'1996-01-01','1996-12-01' UNION ALL
SELECT 1997,'1997-01-01','1997-12-01' UNION ALL
SELECT 1998,'1998-01-01','1998-12-01'
), */
t AS (
SELECT ym.[year],
CASE WHEN a.began > ym.began THEN
a.began
ELSE
ym.began
END began,
CASE WHEN a.[end] < ym.[end] THEN
a.[end]
ELSE
ym.[end]
END [end]
, a.js ,a.zjs -- 切割后的起至月份,验证用,可以删除
FROM ym
JOIN a
ON (a.began BETWEEN ym.began AND ym.[end])
OR (a.[end] BETWEEN ym.began AND ym.[end])
OR (ym.began BETWEEN a.began AND a.[end])
)
SELECT [year],
(DATEDIFF(month,began,[end])+1)*js zjs,
began,
[end]
FROM t
year zjs began end
----------- --------------------------------------- ---------- ----------
1994 1200.00 1994-01-01 1994-12-01
1995 500.00 1995-01-01 1995-05-01
1995 700.00 1995-06-01 1995-12-01
1996 1200.00 1996-01-01 1996-12-01
1997 1200.00 1997-01-01 1997-12-01
1998 300.00 1998-01-01 1998-03-01
;with cte
as(
select began, [end], js from tbl7
union all
select dateadd(MONTH, 1, began), [end],js from cte as a
where not exists(select began, [end],js from tbl7 b
where b.began=DATEADD(MONTH, 1, a.began)
)
and a.began < [end]
)
select YEAR([began]) as '年份', sum(js) as '总基数' from cte group by YEAR([began]), [end]
有最大递归 100 限制....SELECT YEAR(DATEADD(MONTH,T2.number,T1.began))AS[year]
,SUM(T1.js)zjs
FROM a T1
JOIN master..spt_values T2 ON T2.number<=DATEDIFF(MONTH,T1.began,T1.[end])
WHERE T2.type='P'
GROUP BY YEAR(DATEADD(MONTH,T2.number,T1.began)),T1.[began]
ORDER BY 1