SQL Server

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

実行結果はこんな感じ。




スポンサードリンク

  

関連記事