SQL Serverで月末日や月初(1日)の日付を求める式、20日締めなどの締め日を基準とした集計期間の集計を求めるユーザー定義関数とそのサンプルSQLを覚書
データベースのデータを基に月初(1日)から月末までのデータを集計することはよくあること。
また、集計期間は必ずしも月初～月末とは限らず、20日締め(先月21日～今月20日まで)のデータの集計も求められる。
締め期間の集計はつどCase文を使ったのでは長くて面倒なのでユーザー定義関数を作成しておく。
忘れないように覚書。
月初や末日の日付を求めるサンプルSQL
月初や末日などの日付を求めるサンプルSQLを以下に記載する。
何れもSQL Serverの日付関連の関数を使ったSQL文。
先月末日
先月の月末日を求めるSQLの例。
select cast((cast(year(getdate()) as varchar(4))+'-'+cast(month(getdate()) as varchar(2))+'-1') as datetime)-1
今月1日
今月の月初(1日)を求めるSQLの例。
select cast((cast(year(getdate()) as varchar(4))+'-'+cast(month(getdate()) as varchar(2))+'-1') as datetime)
先月の1日
先月の月初(1日)を求めるSQLの例。
select dateadd(m,-1,cast((cast(year(getdate()) as varchar(4))+'-'+cast(month(getdate()) as varchar(2))+'-1') as datetime))
絞め日対応
20日締めを例として、仮に今日が10/22だったとして、9/21～10/20を10月分のデータとする例。
考え方としては、日付の日の部分が21以上だったら月に1を足して、20以下だったら月はそのままとする。
抽出しやすいように9/21～10/21をYYYYMMの文字列として表す。
SQL文の中で直接記述すると長くなるので、日付を引数としてYYYYMMのデータを返すスカラー関数を作成する。
作成した関数をビュー上のフィールドに記述して該当するデータを抽出する。
サンプルの実行環境としてNorthwindサンプルデータベースを利用した。
作成した関数は以下の通り。
CREATE FUNCTION [dbo].[funcshime]
(
-- Add the parameters for the function here
@pdate as datetime
)
RETURNS varchar(6)
AS
BEGIN
-- Declare the return variable here
DECLARE @stryear as char(4),@strmonth as varchar(2),@result as varchar(6)
SELECT @stryear = cast(year(@pdate) as char(4))
IF day(@pdate) >=21
begin
SELECT @strmonth = right('0' + cast((month(@pdate)+1) as varchar(2)),2)
end
else
SELECT @strmonth = right('0' + cast((month(@pdate)) as varchar(2)),2)
SELECT @result =@stryear + @strmonth
RETURN @result
END
以下は、作成した関数を利用したサンプルSQL文。
NorthwindのOrderテーブルを使用(RequiredDateを基準日とした)。
1996年9月度(1996/8/21～1996/9/20)のデータを抽出する例。
SELECT OrderID, RequiredDate, dbo.funcshime(RequiredDate) AS shime_data
FROM dbo.Orders
WHERE (dbo.funcshime(RequiredDate) = '199609')
ORDER BY RequiredDate
実行結果はこんな感じ。
コメント