计算任意两个时间之间的星期几的次数(纵向显示)

session

1. 
   
2. if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_weekdaycount]') and xtype in (N'FN', N'IF', N'TF'))
   
3. drop function [dbo].[f_weekdaycount]
   
4. GO
   
5. /*--计算任意两个时间之间的星期几的次数(纵向显示)
   
6. 本方法直接判断 @@datefirst 做对应处理
   
7. 不受 sp_language 及 set datefirst 的影响  
   
8. -
   
9. /*--调用示例
   
10. 
    
11. select * from f_weekdaycount('2004-8-02','2004-8-8')
    
12. --*/
    
13. create function f_weekdaycount(
    
14. @dt_begin datetime,
    
15. @dt_end datetime
    
16. )returns table
    
17. as
    
18. return(
    
19. select 项目='跨周数'
    
20.   ,值=case when @dt_begin<@dt_end
    
21.    then (datediff(day,@dt_begin,@dt_end)+7)/7
    
22.    else (datediff(day,@dt_end,@dt_begin)+7)/7 end
    
23. union all
    
24. select a.a,case b.a
    
25.   when -1 then case when a.b between b.b and b.c then 1 else 0 end
    
26.   when  0 then case when b.b<=a.b then 1 else 0 end
    
27.    +case when b.c>=a.b then 1 else 0 end
    
28.   else b.a+case when b.b<=a.b then 1 else 0 end
    
29.    +case when b.c>=a.b then 1 else 0 end
    
30.   end
    
31. from(select a='星期一',b=1
    
32.   union all select '星期二',2 union all select '星期三',3
    
33.   union all select '星期四',4 union all select '星期五',5
    
34.   union all select '星期六',6 union all select '星期日',0
    
35. )a,(select a=case when @dt_begin<@dt_end
    
36.    then datediff(week,@dt_begin,@dt_end)-1
    
37.    else datediff(week,@dt_end,@dt_begin)-1 end
    
38.   ,b=case when @dt_begin<@dt_end
    
39.    then (@@datefirst+datepart(weekday,@dt_begin)-1)%7
    
40.    else (@@datefirst+datepart(weekday,@dt_end)-1)%7 end
    
41.   ,c=case when @dt_begin<@dt_end
    
42.    then (@@datefirst+datepart(weekday,@dt_end)-1)%7
    
43.    else (@@datefirst+datepart(weekday,@dt_begin)-1)%7 end)b
    
44. )
    
45. go
    

复制代码