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