declare @a datetime,@b datetime
declare @t table(rq varchar(7))
set @a=min(合同開始日期) from tb
set @b=getdate()
while convert( varchar(7),@a,120)<=convert(varchar(7),@b,120)
begin
insert into @t select convert( varchar(7),@a,120)
select @a=dateadd(mm,1,@a)
end
select a.rq as 月份, sum(isnull(付款金額,0)) as 總數
from @t a left join tb b on a.rq=convert( varchar(7),b.付款日期,120)
group by a.rq
;with t1 as(
select top 12 convert(nvarchar(7),dateadd(mm,-row_number() over(order by id)+1,'2009-5-13'),120) month from syscolumns)select t1.month,sum(isnull(total,0)) total
from t1 left join (
select convert(nvarchra(7),paydate,120) month,sum(paymoney) total
from tb
group by left(paydate,7)
)t2 on t1.month = t2.month
order by t1.month
if object_id('[tb]') is not null drop table [tb]
go create table [tb]([ID] int,[begin] datetime,[end] datetime,[paytime] datetime,[amount] int)
insert [tb] select 1,'2008-10-01','2009-03-01','2009-01-03',10000
union all select 2,'2008-10-01','2009-03-01','2009-01-04',20000
union all select 3,'2008-10-01','2009-03-01','2009-01-05',10000
union all select 4,'2008-10-01','2009-03-01','2009-01-06',10000
union all select 5,'2008-10-01','2009-03-01','2009-04-03',50000
union all select 6,'2008-10-01','2009-03-01','2009-04-03',10000
union all select 7,'2008-10-01','2009-03-01','2009-04-03',10000
---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([序号] int,[合同开始时间] datetime,[合同结束时间] datetime,[付款时间] datetime,[付款金额] int)
insert [tb]
select 1,'2008-10-01','2009-03-01','2009-01-03',10000 union all
select 2,'2008-10-01','2009-03-01','2009-01-04',20000 union all
select 3,'2008-10-01','2009-03-01','2009-01-05',10000 union all
select 4,'2008-10-01','2009-03-01','2009-01-06',10000 union all
select 5,'2008-10-01','2009-03-01','2009-04-03',50000 union all
select 6,'2008-10-01','2009-03-01','2009-04-03',10000 union all
select 7,'2008-10-01','2009-03-01','2009-04-03',10000
---查询---
select
a.月份,
isnull(b.付款金额,0) as 总数
from
(
select
convert(char(7),dateadd(mm,b.number,a.sdate),120) as 月份
from
(select min(合同开始时间) as sdate from tb) a,
master..spt_values b
where
b.type='P'
and
datediff(mm,dateadd(mm,b.number,a.sdate),getdate())>=0
) a
left join
(select convert(char(7),付款时间,120) pdate,sum(付款金额) as 付款金额 from tb group by convert(char(7),付款时间,120)) b
on
a.月份=b.pdate---结果---
月份 总数
------- -----------
2008-10 0
2008-11 0
2008-12 0
2009-01 50000
2009-02 0
2009-03 0
2009-04 70000
2009-05 0(所影响的行数为 8 行)
还有点‘min(合同开始时间)’为什么要找最小的‘合同开始时间’,如果数据库有两个以上的合同,是不是都以最小的‘合同开始时间’开始,如果那样的话还不是我最想要的结果。我马上测试。
有合同号的话,要group by 合同号,每个合同号分月统计用min的意思是如果有合同号相同,但开始时间不一样的话,以最小的为准
1 2008-10-01 2009-03-01 2009-01-03 10000
2 2008-10-01 2009-03-01 2009-01-04 20000
3 2008-10-01 2009-03-01 2009-01-05 10000
4 2008-10-01 2009-03-01 2009-01-06 10000
5 2008-10-01 2009-03-01 2009-04-03 50000
6 2008-10-01 2009-03-01 2009-04-03 10000
7 2008-10-01 2009-03-01 2009-04-03 10000 create table #title
(
序号 int,
合同开始时间 datetime,
合同结束时间 datetime,
付款时间 datetime,
付款金额 int
)create table #temp
(
付款时间 datetime,
付款金额 int
)insert into #title
select 1, '2008-10-01', '2009-03-01', '2009-01-03', 10000 union all
select 2, '2008-10-01', '2009-03-01', '2009-01-04', 20000 union all
select 3, '2008-10-01', '2009-03-01', '2009-01-05', 10000 union all
select 4, '2008-10-01', '2009-03-01', '2009-01-06', 10000 union all
select 5, '2008-10-01', '2009-03-01', '2009-04-03', 50000 union all
select 6, '2008-10-01', '2009-03-01', '2009-04-03', 10000 union all
select 7, '2008-10-01', '2009-03-01', '2009-04-03', 10000 DECLARE @MAXDATE datetime
DEClARE @MINDATE datetime
SELECT @MAXDATE=MAX(合同结束时间) FROM #title group by 合同结束时间
SELECT @MINDATE=MIN(合同开始时间) FROM #title group by 合同开始时间WHILE @MINDATE < GETDATE()
BEGIN
if(not exists (select 1 from (select Convert(nvarchar(7),付款时间,120) 付款时间 from #title group by Convert(nvarchar(7),付款时间,120)) A where 付款时间=convert(nvarchar(7),@MINDATE,120)))
BEGIN
insert into #temp(付款时间,付款金额)values(@MINDATE,0)
END
SET @MINDATE=dateadd(MM,1,@MINDATE)
END
if YEAR(@MINDATE)=YEAR(GETDATE()) AND MONTH(@MINDATE) = MONTH(GETDATE()) AND DAY(@MINDATE)>=DAY(GETDATE())
insert into #temp(付款时间,付款金额)values(@MINDATE,0)
select Convert(nvarchar(7),付款时间,120) 付款时间,sum(付款金额) 付款金额 from #title group by Convert(nvarchar(7),付款时间,120)
union all
select Convert(nvarchar(7),付款时间,120) 付款时间,付款金额 from #tempdrop table #temp