T-SQL: 三個通用的與日期相關的,輔助按周(星期日是周的最後一天)匯總的自訂函數

來源:互聯網
上載者:User
函數
/*
每個函數都只有一句話!
其實都是從我的另一篇 blog 裡摳出來的:

T-SQL 產生 兩個新的真正的西曆年曆
http://blog.csdn.net/playyuer/archive/2004/04/07/2860.aspx


T-SQL 產生一個簡易的 西曆年曆 T-SQL 含日期所在月及年的周次
http://blog.csdn.net/playyuer/archive/2004/04/05/2859.aspx

由於使用了 (@@datefirst + datepart(weekday,@date)) % 7  判斷周幾
因此與 datefirst 無關,且可適應各種語言版本的 SQL Server

*/

--周日算作(上一)周的最後一天

create function udf_WeekOfYear(@date datetime)
--求 @date 所在周是當年的第幾周
--周日算作(上一)周的最後一天
--用於按周匯總 Group by 時,不要有跨年資料,或者同時 Group by year
--group by year(date),month(date), dbo.udf_WeekOfYear(date),dbo.udf_WeekOfMonth(date)
returns int
as
begin
return
(select datediff(week
                 ,case when (@@datefirst + datepart(weekday,dateadd(day,0,datediff(day,0,dateadd(year,datediff(year,0,@date),0))))) % 7 = 1
                            then dateadd(day,-1,dateadd(day,0,datediff(day,0,dateadd(year,datediff(year,0,@date),0))))
                       else dateadd(day,0,datediff(day,0,dateadd(year,datediff(year,0,@date),0))) --date 所在年的第一天 即: 一月一號
                  end
                 ,case when (@@datefirst + datepart(weekday,@date)) % 7 = 1
                            then dateadd(day,-1,@date)
                       else @date
                  end
                ) + 1)
end

go

create function udf_WeekOfMonth(@date datetime)
--求 @date 所在周是當月的第幾周
--周日算作(上一)周的最後一天
--用於按周匯總 Group by 時,不要有跨月跨年資料,或者同時 Group by year,month
--group by year(date),month(date), dbo.udf_WeekOfYear(date),dbo.udf_WeekOfMonth(date)
returns int
as
begin
return
      ( select datediff(week
                 ,case when (@@datefirst + datepart(weekday,dateadd(month,datediff(month,0,@date),0))) % 7 = 1
                            then dateadd(month,datediff(month,0,@date),0) - 1
                       else dateadd(month,datediff(month,0,@date),0)
                  end
                 ,case when (@@datefirst + datepart(weekday,@date)) % 7 = 1
                            then @date-1
                       else @date
                  end
                ) + 1 )
end

go

create function udf_weekday(@ int,@date datetime)
returns datetime
as
begin
/*
--周日算作(上一)周的最後一天
  當 @ <= 1 代表將 @date 映射到 所在周的星期一
  當 @ = 2  代表將 @date 映射到 所在周的星期二
  當 @ = 3  代表將 @date 映射到 所在周的星期三
  當 @ = 4  代表將 @date 映射到 所在周的星期四
  當 @ = 5  代表將 @date 映射到 所在周的星期五
  當 @ = 6  代表將 @date 映射到 所在周的星期六
  當 @ >= 7 代表將 @date 映射到 所在周的星期日
  可用於按周匯總 Group by,均支援跨年跨月資料
*/

return
(select --@date,datename(weekday,@date),(@@datefirst + datepart(weekday,@date)) % 7,3 - (@@datefirst + datepart(weekday,@date)) % 7,
           dateadd(day
                  ,case when (@@datefirst + datepart(weekday,@date)) % 7 = 0 --周六
                              then
                                   case when @ between 1 and 6
                                             then @ - 6
                                        else 1
                                    end
                        when (@@datefirst + datepart(weekday,@date)) % 7 = 1 --周日(七)
                              then
                                   case when @ between 1 and 6
                                             then @ - 7
                                        else 0
                                    end

                        when (@@datefirst + datepart(weekday,@date)) % 7 between 2 and 6 --周一至周五
                              then
                                   case when @ between 1 and 6
                                             then  @ + 1 - (@@datefirst + datepart(weekday,@date)) % 7
                                        else 8 - (@@datefirst + datepart(weekday,@date)) % 7
                                   end
                    end
                  ,@date))
/*
測試:

select date,datename(weekday,date),'映射到:',dbo.udf_weekday(2,date),datename(weekday,dbo.udf_weekday(1,date))
from T
order by date

--===============
set datefirst 4
declare @ int,@a int
set @ = 1


select date,datename(weekday,date),(@@datefirst + datepart(weekday,date)) % 7,3 - (@@datefirst + datepart(weekday,date)) % 7,
           dateadd(day
                  ,case when (@@datefirst + datepart(weekday,date)) % 7 = 0 --周六
                              then
                                   case when @ between 2 and 7
                                             then -(7-@)
                                        else @
                                    end
                        when (@@datefirst + datepart(weekday,date)) % 7 = 1 --周日
                              then
                                   case when @ between 2 and 7
                                             then -(7-@)-1
                                        else @ - 1
                                    end

                        when (@@datefirst + datepart(weekday,date)) % 7 between 2 and 6
                              then
                                   case when @ between 2 and 7
                                             then  @ - (@@datefirst + datepart(weekday,date)) % 7
                                        else 8 - (@@datefirst + datepart(weekday,date)) % 7
                                   end

                                  
                    end
                  ,date)
from d
order by date
*/

end


相關文章

Beyond APAC's No.1 Cloud

19.6% IaaS Market Share in Asia Pacific - Gartner IT Service report, 2018

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。