跟我一起学SQL 第二章2.1计算任意两个时间之间的星期几的次数(横向显示)

session

1. 
   
2. 
   
3. if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_weekdaycount]') and xtype in (N'FN', N'IF', N'TF'))
   
4. drop function [dbo].[f_weekdaycount]
   
5. GO
   
6. /*--计算任意两个时间之间的星期几的次数(横向显示)
   
7. 本方法直接判断 @@datefirst 做对应处理
   
8. 不受 sp_language 及 set datefirst 的影响  
   
9. 
   
10. /*--调用示例
    
11. 
    
12. select * from f_weekdaycount('2004-9-01','2004-9-02')
    
13. --*/
    
14. create function f_weekdaycount(
    
15. @dt_begin datetime,
    
16. @dt_end datetime
    
17. )returns table
    
18. as
    
19. return(
    
20. select 跨周数
    
21.   ,周一=case a
    
22.    when -1 then case when 1 between b and c then 1 else 0 end
    
23.    when  0 then case when b<=1 then 1 else 0 end
    
24.      +case when c>=1 then 1 else 0 end
    
25.    else a+case when b<=1 then 1 else 0 end
    
26.     +case when c>=1 then 1 else 0 end
    
27.    end
    
28.   ,周二=case a
    
29.    when -1 then case when 2 between b and c then 1 else 0 end
    
30.    when  0 then case when b<=2 then 1 else 0 end
    
31.      +case when c>=2 then 1 else 0 end
    
32.    else a+case when b<=2 then 1 else 0 end
    
33.     +case when c>=2 then 1 else 0 end
    
34.    end
    
35.   ,周三=case a
    
36.    when -1 then case when 3 between b and c then 1 else 0 end
    
37.    when  0 then case when b<=3 then 1 else 0 end
    
38.      +case when c>=3 then 1 else 0 end
    
39.    else a+case when b<=3 then 1 else 0 end
    
40.     +case when c>=3 then 1 else 0 end
    
41.    end
    
42.   ,周四=case a
    
43.    when -1 then case when 4 between b and c then 1 else 0 end
    
44.    when  0 then case when b<=4 then 1 else 0 end
    
45.      +case when c>=4 then 1 else 0 end
    
46.    else a+case when b<=4 then 1 else 0 end
    
47.     +case when c>=4 then 1 else 0 end
    
48.    end
    
49.   ,周五=case a
    
50.    when -1 then case when 5 between b and c then 1 else 0 end
    
51.    when  0 then case when b<=5 then 1 else 0 end
    
52.      +case when c>=5 then 1 else 0 end
    
53.    else a+case when b<=5 then 1 else 0 end
    
54.     +case when c>=5 then 1 else 0 end
    
55.    end
    
56.   ,周六=case a
    
57.    when -1 then case when 6 between b and c then 1 else 0 end
    
58.    when  0 then case when b<=6 then 1 else 0 end
    
59.      +case when c>=6 then 1 else 0 end
    
60.    else a+case when b<=6 then 1 else 0 end
    
61.     +case when c>=6 then 1 else 0 end
    
62.    end
    
63.   ,周日=case a
    
64.    when -1 then case when 0 between b and c then 1 else 0 end
    
65.    when  0 then case when b<=0 then 1 else 0 end
    
66.      +case when c>=0 then 1 else 0 end
    
67.    else a+case when b<=0 then 1 else 0 end
    
68.     +case when c>=0 then 1 else 0 end
    
69.    end
    
70. from(
    
71.   select 跨周数=case when @dt_begin<@dt_end
    
72.     then (datediff(day,@dt_begin,@dt_end)+7)/7
    
73.     else (datediff(day,@dt_end,@dt_begin)+7)/7 end
    
74.    ,a=case when @dt_begin<@dt_end
    
75.     then datediff(week,@dt_begin,@dt_end)-1
    
76.     else datediff(week,@dt_end,@dt_begin)-1 end
    
77.    ,b=case when @dt_begin<@dt_end
    
78.     then (@@datefirst+datepart(weekday,@dt_begin)-1)%7
    
79.     else (@@datefirst+datepart(weekday,@dt_end)-1)%7 end
    
80.    ,c=case when @dt_begin<@dt_end
    
81.     then (@@datefirst+datepart(weekday,@dt_end)-1)%7
    
82.     else (@@datefirst+datepart(weekday,@dt_begin)-1)%7 end)a
    
83. )
    
84. go
    

复制代码

站长苏飞

支持一下