sql 实现按月份 季度统计报表

呵呵，我们在处理设计到日期统计时候经常会按照年度，季度，月份统计进行同期比或者环比

先看看表结构

/****** 对象: Table [dbo].[t_case_statistics] 作者：JC_Dreaming 脚本日期: 08/30/ 11:53:15 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[t_case_statistics](

[caseid] [bigint] IDENTITY(1,1) NOT NULL,

[caseapplicant] [varchar](100) COLLATE Chinese_PRC_CI_AS NOT NULL,

[casestartdate] [datetime] NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

其中caseid 主键自增长，caseapplicant为申报人，casestartdate起始时间。

就按照起始时间进行统计。

插入几条测试语句

insert into t_case_statistics (caseapplicant,casestartdate)

values ('权限以上企业投资备案项目上报',convert(DATETIME,'-08-28 13:14:56',111));

insert into t_case_statistics (caseapplicant,casestartdate)

values ('一、二类机动车维修经',convert(DATETIME,'-08-28 14:11:09',111));

insert into t_case_statistics (caseapplicant,casestartdate)

values ('盟工商非公司企业',convert(DATETIME,'-08-28 14:16:10',111));

insert into t_case_statistics (caseapplicant,casestartdate)

values ('利用外国政府贷款',convert(DATETIME,'-08-27 15:13:12',111));

......

呵呵，我们看看数据库组织语句

select years as '年份',

case when months=1 then counts else 0 end '1月份',

case when months=2 then counts else 0 end '2月份',

case when months=3 then counts else 0 end '3月份',

case when quarters=1 then counts else 0 end '一季度',

case when months=4 then counts else 0 end '4月份',

case when months=5 then counts else 0 end '5月份',

case when months=6 then counts else 0 end '6月份',

case when quarters=2 then counts else 0 end '二季度',

case when months=7 then counts else 0 end '7月份',

case when months=8 then counts else 0 end '8月份',

case when months=9 then counts else 0 end '9月份',

case when quarters=3 then counts else 0 end '三季度',

case when months=10 then counts else 0 end '10月份',

case when months=11 then counts else 0 end '11月份',

case when months=12 then counts else 0 end '12月份',

case when quarters=4 then counts else 0 end '四季度'

from(

select datepart(yy,caseStartDate) as years,datepart(q,caseStartDate) as quarters, datepart(mm,caseStartDate) months,count(1)as counts

from ximeng_web.dbo.t_case_statistics group by year(caseStartDate),datepart(q,caseStartDate),month(caseStartDate)

)as test

其中month(caseStartDate) 等效于datepart(mm,caseStartDate)

year(caseStartDate)等效于 datepart(yy,caseStartDate)

可惜sql只提供了这两个统计函数，需要按照其它日期统计就不如datepart()灵活方便自如

统计效果：

[img]F:/1.jpg[/img]