sql server函数介绍-日期类

13、复杂年月处理

--定义基本数字表
declare @T1 table(代码 int,名称 varchar(10),参加时间 datetime,终止时间 datetime)
insert into @T1
select 12,'单位1','2003/04/01','2004/05/01'
union all select 22,'单位2','2001/02/01','2003/02/01'
union all select 42,'单位3','2000/04/01','2003/05/01'
union all select 25,'单位5','2003/04/01','2003/05/01'

--定义年表
declare @NB table(代码 int,名称 varchar(10),年份 int)
insert into @NB
select 12,'单位1',2003
union all select 12,'单位1',2004
union all select 22,'单位2',2001
union all select 22,'单位2',2002
union all select 22,'单位2',2003

--定义月表
declare @YB table(代码 int,名称 varchar(10),年份 int,月份 varchar(2))
insert into @YB
select 12,'单位1',2003,'04'
union all select 22,'单位2',2001,'01'
union all select 22,'单位2',2001,'12'

--为年表+月表数据处理准备临时表
select top 8246 y=identity(int,1753,1)
into #tby from
(select id from syscolumns) a,
(select id from syscolumns) b,
(select id from syscolumns) c

--为月表数据处理准备临时表
select top 12 m=identity(int,1,1)
into #tbm from syscolumns

/**//*--数据处理--*/
--年表数据处理
select a.*
from(
select a.代码,a.名称,年份=b.y
from @T1 a,#tby b
where b.y between year(参加时间) and year(终止时间)
) a left join @NB b on a.代码=b.代码 and a.年份=b.年份
where b.代码 is null

--月表数据处理
select a.*
from(
select a.代码,a.名称,年份=b.y,月份=right('00'+cast(c.m as varchar),2)
from @T1 a,#tby b,#tbm c
where b.y*100+c.m between convert(varchar(6),参加时间,112)
and convert(varchar(6),终止时间,112)
) a left join @YB b on a.代码=b.代码 and a.年份=b.年份 and a.月份=b.月份
where b.代码 is null
order by a.代码,a.名称,a.年份,a.月份

--删除数据处理临时表
drop table #tby,#tbm

14、 交叉表

--示例

--示例数据
create table tb(ID int,Time datetime)
insert tb select 1,'2005/01/24 16:20'
union all select 2,'2005/01/23 22:45'
union all select 3,'2005/01/23 0:30'
union all select 4,'2005/01/21 4:28'
union all select 5,'2005/01/20 13:22'
union all select 6,'2005/01/19 20:30'
union all select 7,'2005/01/19 18:23'
union all select 8,'2005/01/18 9:14'
union all select 9,'2005/01/18 18:04'
go

--查询处理：
select　　 case when grouping(b.Time)=1 then 'Total' else b.Time end,
[Mon]=sum(case a.week when 1 then 1 else 0 end),
[Tue]=sum(case a.week when 2 then 1 else 0 end),
[Wed]=sum(case a.week when 3 then 1 else 0 end),
[Thu]=sum(case a.week when 4 then 1 else 0 end),
[Fri]=sum(case a.week when 5 then 1 else 0 end),
[Sat]=sum(case a.week when 6 then 1 else 0 end),
[Sun]=sum(case a.week when 0 then 1 else 0 end),
[Total]=count(a.week)
from(
select Time=convert(char(5),dateadd(hour,-1,Time),108)
--时间交界点是1am,所以减1小时，避免进行跨天处理
,week=(@@datefirst+datepart(weekday,Time)-1)%7
--考虑@@datefirst对datepart的影响
from tb
)a right join(
select id=1,a='16:00',b='19:59',Time='[5pm - 9pm)' union all
select id=2,a='20:00',b='23:59',Time='[9pm - 1am)' union all
select id=3,a='00:00',b='02:59',Time='[1am - 4am)' union all
select id=4,a='03:00',b='07:29',Time='[4am - 8:30am)' union all
select id=5,a='07:30',b='11:59',Time='[8:30am - 1pm)' union all
select id=6,a='12:00',b='15:59',Time='[1pm - 5pm)'
)b on a.Time>=b.a and a.Time<b.b
group by b.id,b.Time with rollup
having grouping(b.Time)=0 or grouping(b.id)=1
go

--删除测试
drop table tb

/**//*--测试结果

Mon　 Tue　 Wed　 Thu　 Fri　 Sat　 Sun　 Total
-------------- ----- ----- ----- ----- ----- ------ ---- -------
[5pm - 9pm)　　0　　 1　　 2　　 0　　 0　　 0　　 0　　 3
[9pm - 1am)　　0　　 0　　 0　　 0　　 0　　 0　　 2　　 2
[1am - 4am)　　0　　 0　　 0　　 0　　 0　　 0　　 0　　 0
[4am - 8:30am) 0　　 0　　 0　　 0　　 1　　 0　　 0　　 1
[8:30am - 1pm) 0　　 1　　 0　　 0　　 0　　 0　　 0　　 1
[1pm - 5pm)　　1　　 0　　 0　　 1　　 0　　 0　　 0　　 2
Total　　　　　1　　 2　　 2　　 1　　 1　　 0　　 2　　 9

（所影响的行数为 7 行）
--*/