任意两个时间之间的星期几的次数-纵.sql

来自「很好的sql 文档,可能邦你成为sql 高手」· SQL 代码 · 共 50 行

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