做统计就要和时间打交道。。。
一:当前年的月统计
select b.number as [Month], sum(a.A4012) shouru,SUM(a.A4011) feiyong,sum(a.A4012-A4011) lirun
from A040A001 a
right join
(select number from master..spt_values where type='p' and number between 1 and 11) b
on B0110='00172' AND a.A4035>'2011-1-1' AND month(a.A4035)=b.number
group by b.number,year(a.A4035) ORDER BY [Month]
结果:
二:时间段内按月统计
1.首先获得日期表
WITH date AS
(
SELECT convert(char(7),DATEADD(month,number,'2011-07-01'),120) riqi
FROM
master..spt_values
WHERE
type='P'
and
DATEADD(month,number,'2011-07-01')<='2012-02-01'
)
SELECT* FROM date
2.操作
WITH date AS
(
SELECT convert(char(7),DATEADD(month,number,'2011-07-01'),120) riqi
FROM
master..spt_values
WHERE
type='P'
and
DATEADD(month,number,'2011-07-01')<='2012-02-01'
)
select date.riqi , sum(a.A4012) shouru,SUM(a.A4011) feiyong,sum(a.A4012-A4011) lirun
from A040A001 a
right join
date
on B0110='00172' AND a.A4035>'2011-1-1' AND convert(char(7),a.A4035,120)=date.riqi
group by date.riqi