求SQL话语,按年分类并显示月份详细累计数据

求SQL语句,按年分类并显示月份详细累计数据
有数据库TB,字段如下:
ID[自增ID]  KHID[客户ID号]  UserID[用户ID号]  STime[开始时间]  ETime[结束时间]  Money[产生费用]

ID  KHID  UserID    STime       ETime      Money
1    1      1     2009-3-1   2009-3-31     100.0000
2    1      1     2009-4-1   2009-4-30     100.0000
3    1      1     2009-5-1   2009-8-31     200.0000
4    1      1     2009-9-1   2009-9-30     100.0000
5    1      1     2009-10-1  2009-12-31    500.0000
6    1      1     2010-1-1   2010-3-31     200.0000
7    1      1     2010-4-1   2010-9-30     600.0000
8    1      1     2010-10-1  2010-12-31    200.0000
9    1      1     2011-1-1   2011-3-31     100.0000
10   1      1     2011-4-1   2011-10-5     800.0000
11   1      1     2011-10-6  2011-11-31    400.0000

如果输入客户ID号为1,用户ID号为1,查询日期为2010-10-31,希望得到的结果是:

日期               金额        累计金额
2009年                            0
3月1日-31日       100.00        100.00
4月1日-30日       100.00        200.00
5月1日-8月31日    200.00        400.00     
9月1日-30日       100.00        500.00 
 
10月1日-12月31日  500.00        1000.00    
2010年                          1000.00
1月1日-3月31日    200.00        1200.00
4月1日-9月30日    600.00        1800.00

如果查询日期是2010-12-31，那么结果就应该是：

日期               金额        累计金额
2009年                            0
3月1日-31日       100.00        100.00
4月1日-30日       100.00        200.00
5月1日-8月31日    200.00        400.00     
9月1日-30日       100.00        500.00
10月1日-12月31日  500.00        1000.00    
2010年                          1000.00
1月1日-3月31日    200.00        1200.00
4月1日-9月30日    600.00        1800.00
10月1日-12月31日  200.00        2000.00

再如查询日期是2011-10-6，那么结果就是：

日期               金额        累计金额
2009年                            0
3月1日-31日       100.00        100.00
4月1日-30日       100.00        200.00
5月1日-8月31日    200.00        400.00     
9月1日-30日       100.00        500.00
10月1日-12月31日  500.00        1000.00    
2010年                          1000.00 
 
1月1日-3月31日    200.00        1200.00
4月1日-9月30日    600.00        1800.00
10月1日-12月31日  200.00        2000.00
2011年                          2000.00
1月1日-3月31日    100.00        2100.00
4月1日-10月5日    800.00        2900.00

显然最后一条记录是不显示的，因为查询日期与每个记录的结束日期做比较，求这个SQL语句

--> --> (Roy)生成測試數據

if not object_id('Tempdb..#T') is null
    drop table #T
Go
Create table #T([ID] int,[KHID] int,[UserID] int,[STime] Datetime,[ETime] datetime,[Money] decimal(18,4))
Insert #T
select 1,1,1,'2009-3-1',N'2009-3-31',100.0000 union all
select 2,1,1,'2009-4-1',N'2009-4-30',100.0000 union all
select 3,1,1,'2009-5-1',N'2009-8-31',200.0000 union all
select 4,1,1,'2009-9-1',N'2009-9-30',100.0000 union all
select 5,1,1,'2009-10-1',N'2009-12-31',500.0000 union all
select 6,1,1,'2010-1-1',N'2010-3-31',200.0000 union all
select 7,1,1,'2010-4-1',N'2010-9-30',600.0000 union all
select 8,1,1,'2010-10-1',N'2010-12-31',200.0000 union all
select 9,1,1,'2011-1-1',N'2011-3-31',100.0000 union all
select 10,1,1,'2011-4-1',N'2011-10-5',800.0000 union all
select 11,1,1,'2011-10-6',N'2011-11-30',400.0000
Go
declare @dt datetime
set @dt='2010-10-31'

DECLARE @KHID INT,@UserID INT

SELECT @KHID=1,@UserID=1

select [日期]=case when [日期]='' then RTRIM([年份])+'年' else [日期] end, 
 
金额,[累计]
from 
(Select
[年份]=YEAR([STime]),
[日期]=rtrim(DATEPART(m,[STime]))+'月'+rtrim(DATEPART(d,[STime]))+'日'+
case when DATEDIFF(m,[STime],[ETime])>0 then '-'+rtrim(DATEPART(m,[ETime]))+'月' else '-' end+
rtrim(DATEPART(d,[ETime]))+'日',
rtrim([Money]) as  金额 ,
[累计],
MONTH([STime]) as ord
from (select * ,[累计]=(select SUM([Money]) from #T where [STime]<=a.[STime] AND [KHID]=a.KHID AND UserID=a.UserID)from #T as a where [ETime]<=@dt AND KHID=@KHID and UserID=@UserID) as a
union all
select 
[年份]=YEAR([STime])+1,
[日期]='',
[金额]='',
[累计],
0 as ord
from 
(select * ,[累计]=(select SUM([Money]) from #T where [STime]<=a.[STime] AND KHID=a.KHID AND UserID=a.UserID)from #T as a where [ETime]<=@dt AND KHID=@KHID and UserID=@UserID) as a 
where month([ETime])=12 and YEAR([ETime])<YEAR(@dt) AND KHID=@KHID and UserID=@UserID
union all
select [年份]=year(MIN([STime])),[日期]='',[金额]='',[累计]=0,ord=0 from #T where [STime]<@dt AND KHID=@KHID and UserID=@UserID
)t
order by t.[年份],t.ord
GO
/*
日期金额累计
2009年0.0000
3月1日-31日100.0000100.0000
4月1日-30日100.0000200.0000
5月1日-8月31日200.0000400.0000
9月1日-30日100.0000500.0000
10月1日-12月31日500.00001000.0000
2010年1000.0000
1月1日-3月31日200.00001200.0000
4月1日-9月30日600.00001800.0000
*/

查看更多 下一篇