有这么个表
mc 有效期起 有效期止 月定额
a公司 2009-1-1 2009-9-30 5000
b公司 2009-2-1 2009-8-31 8000
c公司 2009-5-1 2009-5-31 10000
. 。
请问如何 用sql求每个自然月的定额总量?谢谢大家了
mc 有效期起 有效期止 月定额
a公司 2009-1-1 2009-9-30 5000
b公司 2009-2-1 2009-8-31 8000
c公司 2009-5-1 2009-5-31 10000
. 。
请问如何 用sql求每个自然月的定额总量?谢谢大家了
from (
select 1 as a
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9
union all
select 10
union all
select 11
union all
select 12
)T1,tb T2
where T1.a between datepart(m,T2.有效期起) and datepart(m,T2.有效期止)
group by T1.a
FROM TB ??
from tb
group by left(convert(varchar(10),有效期起,112),6),left(convert(varchar(10),有效期止,112),6)
select T1.a,sum(T2.月定额)
from ( SELECT NUMBER AS A FROM MASTER..SPT_VALUES WHERE TYPE='P' AND NUMBER BETWEEN 1 AND 12)T1,tb T2
where T1.a between datepart(m,T2.有效期起) and datepart(m,T2.有效期止)
group by T1.a修改糖炒的
go
create table [tb]([mc] varchar(5),[有效期起] datetime,[有效期止] datetime,[月定额] int)
insert [tb]
select 'a公司','2009-1-1','2009-9-30',5000 union all
select 'b公司','2009-2-1','2009-8-31',8000 union all
select 'c公司','2009-5-1','2009-5-31',10000select
convert(varchar(7),dt,120) as 月份,
sum(月定额) as 定额总量
from
(
select
a.mc,
dateadd(month,b.number,a.有效期起) as dt,
a.月定额
from
tb a,
master..spt_values b
where
b.type='P' and dateadd(month,b.number,a.有效期起)<有效期止
) t
group by convert(varchar(7),dt,120)
order by convert(varchar(7),dt,120)
--测试结果:
/*
月份 定额总量
------- -----------
2009-01 5000
2009-02 13000
2009-03 13000
2009-04 13000
2009-05 23000
2009-06 13000
2009-07 13000
2009-08 13000
2009-09 5000(9 行受影响)*/