SQL Serverで月末日や月初(1日)の日付を求める式、20日締めなどの締め日を基準とした集計期間の集計を求めるユーザー定義関数とそのサンプルSQLを覚書
データベースのデータを基に月初(1日)から月末までのデータを集計することはよくあること。
また、集計期間は必ずしも月初～月末とは限らず、20日締め(先月21日～今月20日まで)のデータの集計も求められる。
締め期間の集計はつどCase文を使ったのでは長くて面倒なのでユーザー定義関数を作成しておく。
忘れないように覚書。
月初や末日の日付を求めるサンプルSQL
月初や末日などの日付を求めるサンプルSQLを以下に記載する。
何れもSQL Serverの日付関連の関数を使ったSQL文。
先月末日
先月の月末日を求めるSQLの例。
1 | select cast((cast(year(getdate()) as varchar(4))+'-'+cast(month(getdate()) as varchar(2))+'-1') as datetime)-1 |
今月1日
今月の月初(1日)を求めるSQLの例。
1 | select cast((cast(year(getdate()) as varchar(4))+'-'+cast(month(getdate()) as varchar(2))+'-1') as datetime) |
先月の1日
先月の月初(1日)を求めるSQLの例。
1 | 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サンプルデータベースを利用した。
作成した関数は以下の通り。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | 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)のデータを抽出する例。
1 2 3 4 | SELECT OrderID, RequiredDate, dbo.funcshime(RequiredDate) AS shime_data FROM dbo.Orders WHERE (dbo.funcshime(RequiredDate) = '199609') ORDER BY RequiredDate |
実行結果はこんな感じ。
