任意两个时间之间的星期几的次数-横.sql
drop function [dbo].[f_weekdaycount]
GO
/*--计算任意两个时间之间的星期几的次数(横向显示)
本方法直接判断 @@datefirst 做对应处理
不受 sp_language 及 set datefirst 的影响
--邹建 2004.08(引用请保留此信息)--*/
/*--调用示例
select * from f_weekdaycount('2004-9-01','2004-9-02')
--*/
create function f_weekdaycount(
@dt_begin datetime,
@dt_end datetime
)returns table
as
return(
select 跨周数
,周一=case a
when -1 then case when 1 between b and c then 1 else 0 end
when 0 then case when b<=1 then 1 else 0 end
+case when c>=1 then 1 else 0 end
else a+case when b<=1 then 1 else 0 end
+case when c>=1 then 1 else 0 end
end
,周二=case a
when -1 then case when 2 between b and c then 1 else 0 end
when 0 then case when b<=2 then 1 else 0 end
+case when c>=2 then 1 else 0 end
else a+case when b<=2 then 1 else 0 end
+case when c>=2 then 1 else 0 end
end
,周三=case a
when -1 then case when 3 between b and c then 1 else 0 end
when 0 then case when b<=3 then 1 else 0 end
+case when c>=3 then 1 else 0 end
else a+case when b<=3 then 1 else 0 end
+case when c>=3 then 1 else 0 end
end
,周四=case a
when -1 then case when 4 between b and c then 1 else 0 end
when 0 then case when b<=4 then 1 else 0 end
+case when c>=4 then 1 else 0 end
else a+case when b<=4 then 1 else 0 end
+case when c>=4 then 1 else 0 end
end
,周五=case a
when -1 then case when 5 between b and c then 1 else 0 end
when 0 then case when b<=5 then 1 else 0 end
+case when c>=5 then 1 else 0 end
else a+case when b<=5 then 1 else 0 end
+case when c>=5 then 1 else 0 end
end
,周六=case a
when -1 then case when 6 between b and c then 1 else 0 end
when 0 then case when b<=6 then 1 else 0 end
+case when c>=6 then 1 else 0 end
else a+case when b<=6 then 1 else 0 end
+case when c>=6 then 1 else 0 end
end
,周日=case a
when -1 then case when 0 between b and c then 1 else 0 end
when 0 then case when b<=0 then 1 else 0 end
+case when c>=0 then 1 else 0 end
else a+case when b<=0 then 1 else 0 end
+case when c>=0 then 1 else 0 end
end
from(
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
,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)a
)
go