[SQL] 資料表值建構函式 - 跨欄位彙總

網路上發現的報表彙總問題，問題為申請流程會有三道文件申請

• 每道文件申請彼此之間沒有順序性
• 每道文件申請會有多次通過紀錄，必須找出最早審核通過日期
• 完成三道文件申請後，要顯示最後該文件完成日期

原始資料和預期結果如下

練習時雖然有達到需求但很亂，Google 到該篇文章 - Find MAX value from multiple columns in a SQL Server table，打開資料表值建構函式應用視野，以前對於資料表值建構函式使用都僅止 [SQL] 資料表值建構函式，這次才發現衍伸資料表使用方式，可以達到跨欄位彙總

TSQL 和說明如下

DECLARE @申請文件 TABLE
(
	[文件ID] CHAR(20),
	[顧客ID] CHAR(9),
	[文件類型] CHAR(1),
	[文件申請日期] DATE,
	[文件審核日期] DATE,
	[審核結果] NCHAR(6)
);

INSERT INTO @申請文件 VALUES 
----- 原問題資料
('xxxxxxxxxxxx', 'xxxx-0001', 'A', '2022/01/01', '2022/01/22', N'通過'),
('xxxxxxxxxxxx', 'xxxx-0001', 'A', '2022/02/15', '2022/02/19', N'通過'),
('xxxxxxxxxxxx', 'xxxx-0001', 'A', '2022/03/01', '2022/03/05', N'不通過'),
('xxxxxxxxxxxx', 'xxxx-0001', 'B', '2022/02/01', '2022/02/20', N'不通過'),
('xxxxxxxxxxxx', 'xxxx-0001', 'B', '2022/03/01', '2022/03/22', N'通過'),
('xxxxxxxxxxxx', 'xxxx-0001', 'C', '2022/01/15', '2022/01/22', N'不通過'),
('xxxxxxxxxxxx', 'xxxx-0001', 'C', '2022/02/20', '2022/02/27', N'通過'),
('xxxxxxxxxxxx', 'xxxx-0002', 'A', '2022/05/01', '2022/05/06', N'通過'),
('xxxxxxxxxxxx', 'xxxx-0002', 'B', '2022/05/20', '2022/05/22', N'通過'),
('xxxxxxxxxxxx', 'xxxx-0002', 'B', '2022/05/15', '2022/05/17', N'通過'),
('xxxxxxxxxxxx', 'xxxx-0002', 'C', '2022/05/15', '2022/05/18', N'通過'),
('xxxxxxxxxxxx', 'xxxx-0003', 'A', '2022/03/20', '2022/03/22', N'通過'),
('xxxxxxxxxxxx', 'xxxx-0003', 'B', '2022/03/14', '2022/03/20', N'通過'),
('xxxxxxxxxxxx', 'xxxx-0003', 'B', '2022/06/01', '2022/06/22', N'不通過'),
('xxxxxxxxxxxx', 'xxxx-0003', 'B', '2022/07/30', '2022/07/31', N'通過'),
('xxxxxxxxxxxx', 'xxxx-0003', 'C', '2022/03/14', '2022/03/15', N'通過'),
----- 故意建立一筆沒有完成流程資料
('xxxxxxxxxxxx', 'xxxx-0004', 'A', '2022/03/14', '2022/03/15', N'通過');

WITH CTE AS 
(
  SELECT *
  FROM
    (
      SELECT
        [文件ID] ,
        [顧客ID] ,		
        [文件類型] ,
        [文件審核日期]
      FROM @申請文件
      WHERE [審核結果] = N'通過'
    ) AS P
    PIVOT
    (
      -- 找出 A、B、C 文件申請最早通過日期
      MIN([文件審核日期]) FOR [文件類型] IN ([A] , [B] , [C])
    ) AS PV
)
SELECT 
  T.[文件ID] ,
  T.[顧客ID] ,	
  T.A AS [文件 A 最早通過時間] ,
  T.B AS [文件 B 最早通過時間] ,
  T.C AS [文件 C 最早通過時間] ,
  -- A、B、C 文件申請都過，才算是完整通過
  IIF(A IS NULL OR B IS NULL OR C IS NULL , NULL , LastUpdateDate) AS 最早申請完成時間
FROM
  (
    SELECT
      * ,
      (
        -- 使用衍伸資料表來達到跨欄位取最大日期
        SELECT 
          MAX(LastUpdateDate)
        FROM 
          (
            VALUES (A),(B),(C)            -- A、B、C 為 PIVOT 後的欄位名稱
          ) AS UpdateDate(LastUpdateDate) -- 定義 Table 名稱 (欄位名稱)
      ) AS LastUpdateDate
    FROM CTE
  ) AS T

TSQL 執行結果

 

• 參考資料
• FB 討論
• 資料表值建構函式
• Find MAX value from multiple columns in a SQL Server table