[SQL Server]歯抜けなしの日付のデータを取得したい

[スポンサードリンク]

指定した期間の売上データを取得する。ただし日付は連続していること。

要件

仕様
売上テーブルと、商品マスタの二つのテーブルがあり、下図の内容である。
７月は３日、５日、７日に商品を売り上げている。

上が商品マスタで、下が売上テーブルの内容である。

これを、マトリクス表に表示したい。

出力イメージ

画面定義

画面はこのような定義となっている。
※SSRSのマトリクス表を使用して説明する。

基礎となるSQL

通常はこのようなSQLを書く。

-- 商品マスタ
DECLARE @Item table (
    ItemCd varchar(4),
    ItemNm varchar(100)
)
-- 売上テーブル
DECLARE @uriage table (
    id int,
    UriageDate datetime,
    ItemCd varchar(4),
    Qty int
)
DECLARE @DateFrom varchar(10) = '2017/07/01'
DECLARE @DateTo varchar(10) = '2017/07/10'
;
-- 商品データ
INSERT INTO @Item values ('1001','リンゴ')
INSERT INTO @Item values ('1002','バナナ')

-- 売上データ
INSERT INTO @uriage values (1,'2017/07/03','1001',8)
INSERT INTO @uriage values (2,'2017/07/03','1002',12)
INSERT INTO @uriage values (3,'2017/07/05','1001',6)
INSERT INTO @uriage values (4,'2017/07/07','1002',4)
INSERT INTO @uriage values (5,'2017/07/07','1002',10)
;

SELECT
    FORMAT(LIST.UriageDate,'yyyy/MM/dd') AS DATE,
    LIST.ItemCd,
    ITM.ItemNm,
    SUM(LIST.Qty) AS Qty
FROM
    @uriage LIST
LEFT OUTER JOIN @Item ITM ON
    LIST.ItemCd = ITM.ItemCd 
GROUP BY
    FORMAT(LIST.UriageDate,'yyyy/MM/dd'),
    LIST.ItemCd,
    ITM.ItemNm
ORDER BY
    FORMAT(LIST.UriageDate,'yyyy/MM/dd'),
    LIST.ItemCd

-- 商品マスタ

DECLARE @Item table (

ItemCd varchar(4),

ItemNm varchar(100)

)

-- 売上テーブル

DECLARE @uriage table (

id int,

UriageDate datetime,

ItemCd varchar(4),

Qty int

)

DECLARE @DateFrom varchar(10) = '2017/07/01'

DECLARE @DateTo varchar(10) = '2017/07/10'

;

-- 商品データ

INSERT INTO @Item values ('1001','リンゴ')

INSERT INTO @Item values ('1002','バナナ')

-- 売上データ

INSERT INTO @uriage values (1,'2017/07/03','1001',8)

INSERT INTO @uriage values (2,'2017/07/03','1002',12)

INSERT INTO @uriage values (3,'2017/07/05','1001',6)

INSERT INTO @uriage values (4,'2017/07/07','1002',4)

INSERT INTO @uriage values (5,'2017/07/07','1002',10)

;

SELECT

FORMAT(LIST.UriageDate,'yyyy/MM/dd') AS DATE,

LIST.ItemCd,

ITM.ItemNm,

SUM(LIST.Qty) AS Qty

FROM

@uriage LIST

LEFT OUTER JOIN @Item ITM ON

LIST.ItemCd = ITM.ItemCd

GROUP BY

FORMAT(LIST.UriageDate,'yyyy/MM/dd'),

LIST.ItemCd,

ITM.ItemNm

ORDER BY

FORMAT(LIST.UriageDate,'yyyy/MM/dd'),

LIST.ItemCd

しかしこれだと、結果には３日、５日、７日のデータしか表示されない。

解決法

マトリクス列に７月１日～７月１０日までを表示させる方法。

日付テーブルと品目テーブルを作成

下図のような結果を出す日付テーブルと品目テーブルを用意する。

日付テーブルの作り方は、
[SQL Server]CTE式の再帰呼び出しを利用して日付テーブルを作成する
を参考にすること。

この日付テーブルと後述する品目日付テーブルを「WHILE」ループで作ろうとすると、パフォーマンスが遅くなるので、ループで作ることはしないこと。

品目テーブルは、商品CDと商品名を、売上テーブルから取得する。
売上テーブルに存在する商品のみで、マトリクス表に出力したいからである。
この品目テーブルはDISTINCT句で重複を除いておく。

この品目テーブルと、日付テーブルを交差結合（CROSS JOIN）して、「品目日付テーブル」を作る。

品目日付テーブルに数量を結合する

下図が品目日付テーブルに数量を結合したものである。

ソースコードと出力結果

この解決法のソースコードと出力結果はこうなる。
売上がない日は数量は「NULL」として、レコードを作成するため、表示データ上は消えない。

ソースコード

-- 商品マスタ
DECLARE @Item table (
    ItemCd varchar(4),
    ItemNm varchar(100)
)
-- 売上テーブル
DECLARE @uriage table (
    id int,
    UriageDate datetime,
    ItemCd varchar(4),
    Qty int
)
DECLARE @DateFrom varchar(10) = '2017/07/01'
DECLARE @DateTo varchar(10) = '2017/07/10'
;
-- 商品データ
INSERT INTO @Item values ('1001','リンゴ')
INSERT INTO @Item values ('1002','バナナ')

-- 売上データ
INSERT INTO @uriage values (1,'2017/07/03','1001',8)
INSERT INTO @uriage values (2,'2017/07/03','1002',12)
INSERT INTO @uriage values (3,'2017/07/05','1001',6)
INSERT INTO @uriage values (4,'2017/07/07','1002',4)
INSERT INTO @uriage values (5,'2017/07/07','1002',10)
;
-- 日付ﾃｰﾌﾞﾙ
WITH date_table AS (
    SELECT
        @DateFrom AS date_value
    UNION ALL
    SELECT
        CAST(FORMAT(DATEADD(DD,1,CAST(date_value as datetime)),'yyyy/MM/dd') as varchar(10))
    FROM date_table
    WHERE date_value < @DateTo
),
-- 品目テーブル
item_list_base AS (
    SELECT DISTINCT
        URI.ItemCd,
        ITM.ItemNm
    FROM
        @uriage URI
    LEFT OUTER JOIN @Item ITM ON
        URI.ItemCd = ITM.ItemCd
    WHERE
        FORMAT(URI.UriageDate,'yyyy/MM/dd')
        BETWEEN
            @DateFrom
            AND
            @DateTo
),
-- 品目日付テーブル
item_list AS (
    SELECT
        DATE_TABLE.date_value AS DATE,
        BASE.ItemCd,
        BASE.ItemNm
    FROM
        item_list_base BASE
    CROSS JOIN date_table DATE_TABLE
)

SELECT
    LIST.DATE,
    LIST.ItemCd,
    LIST.ItemNm,
    SUM(URI.Qty) AS Qty
FROM
    item_list LIST
LEFT OUTER JOIN @uriage URI ON
    LIST.DATE = FORMAT(URI.UriageDate,'yyyy/MM/dd') AND
    LIST.ItemCd = URI.ItemCd 
GROUP BY
    LIST.DATE,
    LIST.ItemCd,
    LIST.ItemNm
ORDER BY
    LIST.DATE,
    LIST.ItemCd

-- 商品マスタ

DECLARE @Item table (

ItemCd varchar(4),

ItemNm varchar(100)

)

-- 売上テーブル

DECLARE @uriage table (

id int,

UriageDate datetime,

ItemCd varchar(4),

Qty int

)

DECLARE @DateFrom varchar(10) = '2017/07/01'

DECLARE @DateTo varchar(10) = '2017/07/10'

;

-- 商品データ

INSERT INTO @Item values ('1001','リンゴ')

INSERT INTO @Item values ('1002','バナナ')

-- 売上データ

INSERT INTO @uriage values (1,'2017/07/03','1001',8)

INSERT INTO @uriage values (2,'2017/07/03','1002',12)

INSERT INTO @uriage values (3,'2017/07/05','1001',6)

INSERT INTO @uriage values (4,'2017/07/07','1002',4)

INSERT INTO @uriage values (5,'2017/07/07','1002',10)

;

-- 日付ﾃｰﾌﾞﾙ

WITH date_table AS (

SELECT

@DateFrom AS date_value

UNION ALL

SELECT

CAST(FORMAT(DATEADD(DD,1,CAST(date_value as datetime)),'yyyy/MM/dd') as varchar(10))

FROM date_table

WHERE date_value < @DateTo

-- 品目テーブル

item_list_base AS (

SELECT DISTINCT

URI.ItemCd,

ITM.ItemNm

FROM

@uriage URI

LEFT OUTER JOIN @Item ITM ON

URI.ItemCd = ITM.ItemCd

WHERE

FORMAT(URI.UriageDate,'yyyy/MM/dd')

BETWEEN

@DateFrom

AND

@DateTo

-- 品目日付テーブル

item_list AS (

SELECT

DATE_TABLE.date_value AS DATE,

BASE.ItemCd,

BASE.ItemNm

FROM

item_list_base BASE

CROSS JOIN date_table DATE_TABLE

)

SELECT

LIST.DATE,

LIST.ItemCd,

LIST.ItemNm,

SUM(URI.Qty) AS Qty

FROM

item_list LIST

LEFT OUTER JOIN @uriage URI ON

LIST.DATE = FORMAT(URI.UriageDate,'yyyy/MM/dd') AND

LIST.ItemCd = URI.ItemCd

GROUP BY

LIST.DATE,

LIST.ItemCd,

LIST.ItemNm

ORDER BY

LIST.DATE,

LIST.ItemCd

出力結果

[スポンサードリンク]

IT☆ぱらだいす

全てのITエンジニアの役に立ちたい