我正在構建一個報告，該報告將捕獲負責任的開始和結束日期中的所有差距和重疊。
想法是有一個列（狀態），它將有 5 個輸出（我理解，一個記錄可能有多個場景，但我需要按以下順序分配它們：
- 無責任- 當根本沒有關聯的責任時（ResponsibleId 或 ResponsibleName 為 NULL）
- No Current Responsible - 在與客戶端關聯的所有記錄中，ResponsibleEndDate 不為 NULL（表示當前）。
- 重疊- 當前一個責任人的責任結束日期與下一個責任人的責任開始日期重疊時。
- 差距- 前一個責任人的責任結束日期和下一個責任人的責任開始日期之間的差距。
- 有效- 前一個負責人的 ResponsibleEndDate 與下一個負責人的 ResponsibleStartDate 之間相差 1 天。
CREATE TABLE example
(ClientId INT, ClientName VARCHAR(100), ResponsibleId INT, ResponsibleName VARCHAR(100), ResponsibleStartDate DATE, ResponsibleEndDate DATE);
INSERT INTO example
VALUES
(123, 'John Smith', NULL, NULL, NULL, NULL),
(234, 'Thomas Anderson', 12345, 'Tom Cruise', '2019-04-13', '2020-09-15'),
(234, 'Thomas Anderson', 23456, 'John Travolta', '2020-09-16', '2022-01-15'),
(234, 'Thomas Anderson', 37890, 'Van Damm', '2022-01-16', NULL),
(345, 'Mary Tron', NULL, NULL, NULL, NULL),
(456, 'Jackie Chan', 56789, 'Leo Messi', '2018-05-18', '2022-01-18'),
(567, 'Cristiano Ronaldo', 12345, 'Tom Cruise', '2019-05-28', '2021-08-20'),
(567, 'Cristiano Ronaldo', 37890, 'Van Damm', '2021-07-15', '2022-01-15'),
(567, 'Cristiano Ronaldo', 17956, 'Harry Potter', '2022-01-25', NULL)
SELECT * FROM example
我需要的輸出：
| 客戶 ID | 客戶名稱 | 責任人ID | 負責人姓名 | 責任開始日期 | 責任結束日期 | 地位 |
|---|---|---|---|---|---|---|
| 123 | 約翰·史密斯 | 空值 | 空值 | 空值 | 空值 | 不負責任 |
| 234 | 托馬斯·安德森 | 12345 | 湯姆·克魯斯 | 2019-04-13 | 2020-09-15 | 有效的 |
| 234 | 托馬斯·安德森 | 23456 | 約翰特拉沃爾塔 | 2020-09-16 | 2022-01-15 | 有效的 |
| 234 | 托馬斯·安德森 | 37890 | 范達姆 | 2022-01-16 | 空值 | 有效的 |
| 345 | 瑪麗特隆 | 空值 | 空值 | 空值 | 空值 | 不負責任 |
| 456 | 成龍 | 56789 | 里奧·梅西 | 2018-05-18 | 2022-01-18 | 當前無責任 |
| 567 | 克里斯蒂亞諾·羅納爾多 | 12345 | 湯姆·克魯斯 | 2019-05-28 | 2021-08-20 | 重疊 |
| 567 | 克里斯蒂亞諾·羅納爾多 | 37890 | 范達姆 | 2021-07-15 | 2022-01-15 | 差距 |
| 567 | 克里斯蒂亞諾·羅納爾多 | 17956 | 哈利波特 | 2022-01-25 | 空值 | 差距 |
我能達到的唯一條件是第一個：
SELECT *,
CASE
WHEN ResponsibleId IS NULL THEN 'No Responsible'
END AS [Status]
FROM example
uj5u.com熱心網友回復：
這是一個好的開始：
declare @example table
(ClientId INT, ClientName VARCHAR(100), ResponsibleId INT, ResponsibleName VARCHAR(100), ResponsibleStartDate DATE, ResponsibleEndDate DATE);
INSERT INTO @example
VALUES
(123, 'John Smith', NULL, NULL, NULL, NULL),
(234, 'Thomas Anderson', 12345, 'Tom Cruise', '2019-04-13', '2020-09-15'),
(234, 'Thomas Anderson', 23456, 'John Travolta', '2020-09-16', '2022-01-15'),
(234, 'Thomas Anderson', 37890, 'Van Damm', '2022-01-16', NULL),
(345, 'Mary Tron', NULL, NULL, NULL, NULL),
(456, 'Jackie Chan', 56789, 'Leo Messi', '2018-05-18', '2022-01-18'),
(567, 'Cristiano Ronaldo', 12345, 'Tom Cruise', '2019-05-28', '2021-08-20'),
(567, 'Cristiano Ronaldo', 37890, 'Van Damm', '2021-07-15', '2022-01-15'),
(567, 'Cristiano Ronaldo', 17956, 'Harry Potter', '2022-01-25', NULL)
;WITH CTE AS
(
SELECT *,
--ROW_NUMBER() OVER(PARTITION BY ClientName ORDER BY COALESCE(ResponsibleStartDate, ResponsibleEndDate)) DN,
LEAD(ResponsibleStartDate)OVER(PARTITION BY ClientName ORDER BY COALESCE(ResponsibleStartDate, ResponsibleEndDate)) NEXT_START_DATE,
LAG(ResponsibleEndDate)OVER(PARTITION BY ClientName ORDER BY COALESCE(ResponsibleStartDate, ResponsibleEndDate)) PREV_END_DATE
FROM @example
)
select *,
CASE
WHEN ResponsibleName IS NULL THEN 'No Responsible'
WHEN PREV_END_DATE IS NOT NULL AND ResponsibleStartDate < PREV_END_DATE then 'OVERLAP'
WHEN PREV_END_DATE IS NOT NULL AND DATEDIFF(dd,PREV_END_DATE, ResponsibleStartDate) = 1 then 'VALID'
WHEN PREV_END_DATE IS NULL then 'VALID'
WHEN PREV_END_DATE IS NOT NULL AND ResponsibleStartDate > PREV_END_DATE then 'GAP'
ELSE 'CASE'
end [STATUS]
from
CTE;
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/424985.html
上一篇：選擇相同外鍵出現次數超過X次的行并忽略最近的X個（時間戳）
下一篇：用于查找多列匹配（重復）的SQL