COUNT関数とDATEDIFF関数を使って営業日数を数える

2024-04-08

SQLで2つの日付間の営業日数を数える

必要なもの

SQL Server Management Studio (SSMS) またはその他の SQL クエリエディタ
テーブル Holidays (オプション)

手順

営業日カレンダーを作成する

まず、営業日と休日の情報を含むテーブルが必要です。このテーブルは、手動で作成することも、スクリプトを使用して生成することもできます。

CREATE TABLE Holidays (
  Date date NOT NULL,
  IsHoliday bit NOT NULL
);

例：

INSERT INTO Holidays (Date, IsHoliday) VALUES
('2024-01-01', 1),
('2024-01-15', 1),
('2024-12-25', 1),
('2024-12-31', 1);

2つの日付間の営業日数を数える

次のクエリは、2つの日付間の営業日数を数えます。

SELECT COUNT(*) AS WorkDays
FROM (
  SELECT Date
  FROM Calendar
  WHERE Date BETWEEN @StartDate AND @EndDate
  AND NOT EXISTS (SELECT * FROM Holidays WHERE Date = Calendar.Date)
) AS WorkDays;

このクエリは、まず Calendar テーブルから開始日と終了日の間のすべての日期を取得します。次に、Holidays テーブルを使用して、取得した日付のうち休日を除外します。最後に、残りの日付の数を数えます。

パラメータ:

@StartDate: 開始日
@EndDate: 終了日

例:

DECLARE @StartDate date = '2024-04-01';
DECLARE @EndDate date = '2024-04-15';

SELECT COUNT(*) AS WorkDays
FROM (
  SELECT Date
  FROM Calendar
  WHERE Date BETWEEN @StartDate AND @EndDate
  AND NOT EXISTS (SELECT * FROM Holidays WHERE Date = Calendar.Date)
) AS WorkDays;

このクエリは、2024年4月1日から2024年4月15日までの営業日数を返します。

注意事項

このクエリは、日曜日は休日と仮定しています。
祝日が土曜日または日曜日の場合は、手動で調整する必要があります。
より複雑な要件の場合は、より複雑なクエリが必要になる場合があります。

コード:

USE [YourDatabase]

-- テーブル作成
CREATE TABLE Holidays (
  Date date NOT NULL,
  IsHoliday bit NOT NULL
);

-- 祝日データ挿入
INSERT INTO Holidays (Date, IsHoliday) VALUES
('2024-01-01', 1),
('2024-01-15', 1),
('2024-12-25', 1),
('2024-12-31', 1);

-- 営業日カレンダー作成
CREATE TABLE Calendar (
  Date date NOT NULL
);

-- 2024年のすべての日期を挿入
DECLARE @StartDate date = '2024-01-01';
DECLARE @EndDate date = '2024-12-31';

WHILE @StartDate <= @EndDate
BEGIN
  INSERT INTO Calendar (Date)
  VALUES (@StartDate);

  SET @StartDate = DATEADD(@StartDate, DAY, 1);
END;

-- 2つの日付間の営業日数を数える
DECLARE @StartDate date = '2024-04-01';
DECLARE @EndDate date = '2024-04-15';

SELECT COUNT(*) AS WorkDays
FROM (
  SELECT Date
  FROM Calendar
  WHERE Date BETWEEN @StartDate AND @EndDate
  AND NOT EXISTS (SELECT * FROM Holidays WHERE Date = Calendar.Date)
) AS WorkDays;

出力:

WorkDays
-------
10

2つの日付間の営業日数を数える他の方法

カレンダーテーブルを使用する

事前に作成されたカレンダーテーブルを使用する方法があります。このテーブルには、すべての日期とそれが営業日かどうかを示すフラグが含まれています。

SELECT COUNT(*) AS WorkDays
FROM Calendar
WHERE Date BETWEEN @StartDate AND @EndDate
AND IsHoliday = 0;

メリット:

シンプルでわかりやすい
高速に実行できる

カレンダーテーブルを事前に作成する必要がある
祝日の追加や削除が難しい

T-SQL の関数を使用する

T-SQL には、営業日数を計算する関数があります。

DATEADD 関数: 指定された日付に営業日数を追加または減算します。
DATEDIFF 関数: 2つの日付間の営業日数の差を計算します。

SELECT DATEDIFF(dd, @StartDate, @EndDate) - COUNT(Date) AS WorkDays
FROM Calendar
WHERE Date BETWEEN @StartDate AND @EndDate
AND IsHoliday = 1;

コードが簡潔になる

複雑な要件には対応できない

ユーザー定義関数を作成する

複雑な要件の場合は、ユーザー定義関数を作成することができます。

CREATE FUNCTION [dbo].[GetWorkDays]
(
  @StartDate date,
  @EndDate date
)
RETURNS int
AS
BEGIN
  DECLARE @WorkDays int = 0;

  WHILE @StartDate <= @EndDate
  BEGIN
    IF NOT EXISTS (SELECT * FROM Holidays WHERE Date = @StartDate)
    BEGIN
      SET @WorkDays = @WorkDays + 1;
    END

    SET @StartDate = DATEADD(@StartDate, DAY, 1);
  END

  RETURN @WorkDays;
END;

SELECT [dbo].[GetWorkDays]('2024-04-01', '2024-04-15');