sql server函数介绍-日期类
2009-11-19 15:40:14 来源:WEB开发网16、任意两个时间之间的星期几的次数-纵
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_weekdaycount]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_weekdaycount]
GO
/**//*--计算任意两个时间之间的星期几的次数(纵向显示)
本方法直接判断 @@datefirst 做对应处理
不受 sp_language 及 set datefirst 的影响
--邹建 2004.08(引用请保留此信息)--*/
/**//*--调用示例
select * from f_weekdaycount('2004-8-02','2004-8-8')
--*/
create function f_weekdaycount(
@dt_begin datetime,
@dt_end datetime
)returns table
as
return(
select 项目='跨周数'
,值=case when @dt_begin<@dt_end
then (datediff(day,@dt_begin,@dt_end)+7)/7
else (datediff(day,@dt_end,@dt_begin)+7)/7 end
union all
select a.a,case b.a
when -1 then case when a.b between b.b and b.c then 1 else 0 end
when 0 then case when b.b<=a.b then 1 else 0 end
+case when b.c>=a.b then 1 else 0 end
else b.a+case when b.b<=a.b then 1 else 0 end
+case when b.c>=a.b then 1 else 0 end
end
from(select a='星期一',b=1
union all select '星期二',2 union all select '星期三',3
union all select '星期四',4 union all select '星期五',5
union all select '星期六',6 union all select '星期日',0
)a,(select a=case when @dt_begin<@dt_end
then datediff(week,@dt_begin,@dt_end)-1
else datediff(week,@dt_end,@dt_begin)-1 end
,b=case when @dt_begin<@dt_end
then (@@datefirst+datepart(weekday,@dt_begin)-1)%7
else (@@datefirst+datepart(weekday,@dt_end)-1)%7 end
,c=case when @dt_begin<@dt_end
then (@@datefirst+datepart(weekday,@dt_end)-1)%7
else (@@datefirst+datepart(weekday,@dt_begin)-1)%7 end)b
)
go
17、统计--交叉表+日期+优先
--交叉表,根据优先级取数据,日期处理
create table tb(qid int,rid nvarchar(4),tagname nvarchar(10),starttime smalldatetime,endtime smalldatetime,startweekday int,endweekday int,startdate smalldatetime,enddate smalldatetime,d int)
insert tb select 1,'A1','未订','08:00','09:00',1 ,5 ,null ,null ,1
union all select 1,'A1','未订','09:00','10:00',1 ,5 ,null ,null ,1
union all select 1,'A1','未订','10:00','11:00',1 ,5 ,null ,null ,1
union all select 1,'A1','装修','08:00','09:00',null,null,'2005-1-18','2005-1-19',2
--union all select 1,'A1','装修','09:00','10:00',null,null,'2005-1-18','2005-1-19',2
union all select 1,'A1','装修','10:00','11:00',null,null,'2005-1-18','2005-1-19',2
union all select 1,'A2','未订','08:00','09:00',1 ,5 ,null ,null ,1
union all select 1,'A2','未订','09:00','10:00',1 ,5 ,null ,null ,1
union all select 1,'A2','未订','10:00','11:00',1 ,5 ,null ,null ,1
--union all select 1,'A2','装修','08:00','09:00',null,null,'2005-1-18','2005-1-19',2
union all select 1,'A2','装修','09:00','10:00',null,null,'2005-1-18','2005-1-19',2
--union all select 1,'A2','装修','10:00','11:00',null,null,'2005-1-18','2005-1-19',2
go
/**//*--楼主这个问题要考虑几个方面
1. 取星期时,set datefirst 的影响
2. 优先级问题
3. qid,rid 应该是未知的(动态变化的)
--*/
--实现的存储过程如下
create proc p_qry
@date smalldatetime --要查询的日期
as
set nocount on
declare @week int,@s nvarchar(4000)
--格式化日期和得到星期
select @date=convert(char(10),@date,120)
,@week=(@@datefirst+datepart(weekday,@date)-1)%7
,@s=''
select id=identity(int),* into #t
from(
select top 100 percent
qid,rid,tagname,
starttime=convert(char(5),starttime,108),
endtime=convert(char(5),endtime,108)
from tb
where (@week between startweekday and endweekday)
or(@date between startdate and enddate)
order by qid,rid,starttime,d desc)a
select @s=@s+N',['+rtrim(rid)
+N']=max(case when qid='+rtrim(qid)
+N' and rid=N'''+rtrim(rid)
+N''' then tagname else N'''' end)'
from #t group by qid,rid
exec('
select starttime,endtime'+@s+'
from #t a
where not exists(
select * from #t
where qid=a.qid and rid=a.rid
and starttime=a.starttime
and endtime=a.endtime
and id<a.id)
group by starttime,endtime')
go
--调用
exec p_qry '2005-1-17'
exec p_qry '2005-1-18'
go
--删除测试
drop table tb
drop proc p_qry
/**//*--测试结果
starttime endtime A1 A2
--------- ------- ---------- ----------
08:00 09:00 未订 未订
09:00 10:00 未订 未订
10:00 11:00 未订 未订
starttime endtime A1 A2
--------- ------- ---------- ----------
08:00 09:00 装修 未订
09:00 10:00 未订 装修
10:00 11:00 装修 未订
--*/
- ››sql server自动生成批量执行SQL脚本的批处理
- ››sql server 2008亿万数据性能优化
- ››SQL Server 2008清空数据库日志方法
- ››sqlserver安装和简单的使用
- ››SQL Sever 2008 R2 数据库管理
- ››SQL SERVER无法安装成功,sqlstp.log文件提示[未发...
- ››Sql Server中通过父记录查找出所有关联的子记录
- ››SqlServer触发器、存储过程和函数
- ››SQL Server 中的事务(含义,属性,管理)
- ››Sqlite数据库插入和读取图片数据
- ››Sql server 2005拒绝了对对象 'xx表' (数...
- ››Sql server 2005拒绝了对对象 'xx表' (数...
更多精彩
赞助商链接