createfunctionudf_WeekOfYear(@datedatetime)
--求@date地点周是昔时的第几周
--周日算作(上一)周的最初一天
--用于按周汇总Groupby时,不要有跨年纪据,大概同时Groupbyyear
--groupbyyear(date),month(date),dbo.udf_WeekOfYear(date),dbo.udf_WeekOfMonth(date)
returnsint
as
begin
return
(selectdatediff(week
,casewhen(@@datefirst+datepart(weekday,dateadd(day,0,datediff(day,0,dateadd(year,datediff(year,0,@date),0)))))%7=1
thendateadd(day,-1,dateadd(day,0,datediff(day,0,dateadd(year,datediff(year,0,@date),0))))
elsedateadd(day,0,datediff(day,0,dateadd(year,datediff(year,0,@date),0)))--date地点年的第一天即:一月一号
end
,casewhen(@@datefirst+datepart(weekday,@date))%7=1
thendateadd(day,-1,@date)
else@date
end
)+1)
end
go
createfunctionudf_WeekOfMonth(@datedatetime)
--求@date地点周是当月的第几周
--周日算作(上一)周的最初一天
--用于按周汇总Groupby时,不要有跨月跨年纪据,大概同时Groupbyyear,month
--groupbyyear(date),month(date),dbo.udf_WeekOfYear(date),dbo.udf_WeekOfMonth(date)
returnsint
as
begin
return
(selectdatediff(week
,casewhen(@@datefirst+datepart(weekday,dateadd(month,datediff(month,0,@date),0)))%7=1
thendateadd(month,datediff(month,0,@date),0)-1
elsedateadd(month,datediff(month,0,@date),0)
end
,casewhen(@@datefirst+datepart(weekday,@date))%7=1
then@date-1
else@date
end
)+1)
end
go
createfunctionudf_weekday(@int,@datedatetime)
returnsdatetime
as
begin
/*
--周日算作(上一)周的最初一天
当@<=1代表将@date映照到地点周的礼拜一
当@=2代表将@date映照到地点周的礼拜二
当@=3代表将@date映照到地点周的礼拜三
当@=4代表将@date映照到地点周的木曜日
当@=5代表将@date映照到地点周的礼拜五
当@=6代表将@date映照到地点周的礼拜六
当@>=7代表将@date映照到地点周的日曜日
可用于按周汇总Groupby,均撑持跨年跨月数据
*/
return
(select--@date,datename(weekday,@date),(@@datefirst+datepart(weekday,@date))%7,3-(@@datefirst+datepart(weekday,@date))%7,
dateadd(day
,casewhen(@@datefirst+datepart(weekday,@date))%7=0--周六
then
casewhen@between1and6
then@-6
else1
end
when(@@datefirst+datepart(weekday,@date))%7=1--周日(七)
then
casewhen@between1and6
then@-7
else0
end
when(@@datefirst+datepart(weekday,@date))%7between2and6--周一至周五
then
casewhen@between1and6
then@+1-(@@datefirst+datepart(weekday,@date))%7
else8-(@@datefirst+datepart(weekday,@date))%7
end
end
,@date))
/*
测试:
selectdate,datename(weekday,date),(@@datefirst+datepart(weekday,date))%7,3-(@@datefirst+datepart(weekday,date))%7,
dateadd(day
,casewhen(@@datefirst+datepart(weekday,date))%7=0--周六
then
casewhen@between2and7
then-(7-@)
else@
end
when(@@datefirst+datepart(weekday,date))%7=1--周日
then
casewhen@between2and7
then-(7-@)-1
else@-1
end
when(@@datefirst+datepart(weekday,date))%7between2and6
then
casewhen@between2and7
then@-(@@datefirst+datepart(weekday,date))%7
else8-(@@datefirst+datepart(weekday,date))%7
end