By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE da (
Name nvarchar(20),
SetDate date,
Code nvarchar(5));
INSERT INTO da (Name, SetDate,Code) VALUES
(N'大白','2024/09/1','A'),(N'大白','2024/09/2','A'),(N'大白','2024/09/3','A'),(N'大白','2024/09/4','A')
,(N'大白','2024/09/5',N'休'),(N'大白','2024/09/6',N'休'),(N'大白','2024/09/7','A'),(N'大白','2024/09/8','A')
,(N'大白','2024/09/9','A'),(N'大白','2024/09/10',N'休'),(N'大白','2024/09/11','A'),(N'大白','2024/09/12',N'休')
,(N'大白','2024/09/13',N'休'),(N'大白','2024/09/14','B'),(N'大白','2024/09/15','B'),(N'大白','2024/09/16','B')
,(N'大白','2024/09/17','B'),(N'大白','2024/09/18','B'),(N'大白','2024/09/19',N'休'),(N'大白','2024/09/20',N'休')
,(N'大白','2024/09/21','A'),(N'大白','2024/09/22','A'),(N'大白','2024/09/23','A'),(N'大白','2024/09/24','A')
,(N'大白','2024/09/25','A'),(N'大白','2024/09/26',N'休'),(N'大白','2024/09/27',N'休'),(N'大白','2024/09/28','A')
,(N'大白','2024/09/29','A'),(N'大白','2024/09/30','A')
,(N'大白','2024/10/1',N'特'),(N'大白','2024/10/2',N'特'),(N'大白','2024/10/3','B'),(N'大白','2024/10/4','B')
,(N'大白','2024/10/5',N'休'),(N'大白','2024/10/6',N'休'),(N'大白','2024/10/7','B'),(N'大白','2024/10/8','B')
,(N'大白','2024/10/9','B'),(N'大白','2024/10/10',N'休'),(N'大白','2024/10/11','B'),(N'大白','2024/10/12',N'休')
,(N'大白','2024/10/13',N'休'),(N'大白','2024/10/14','A'),(N'大白','2024/10/15','A'),(N'大白','2024/10/16','A')
,(N'大白','2024/10/17','A'),(N'大白','2024/10/18','A'),(N'大白','2024/10/19',N'休'),(N'大白','2024/10/20',N'休')
,(N'大白','2024/10/21','B'),(N'大白','2024/10/22','B'),(N'大白','2024/10/23','B'),(N'大白','2024/10/24','B')
,(N'大白','2024/10/25','B'),(N'大白','2024/10/26',N'休'),(N'大白','2024/10/27',N'休'),(N'大白','2024/10/28','B')
,(N'大白','2024/10/29','B'),(N'大白','2024/10/30','B'),(N'大白','2024/10/31','B')
,(N'小明','2024/10/1','A'),(N'小明','2024/10/2','A'),(N'小明','2024/10/3','A'),(N'小明','2024/10/4','A')
,(N'小明','2024/10/5',N'休'),(N'小明','2024/10/6',N'休'),(N'小明','2024/10/7','A'),(N'小明','2024/10/8','A')
,(N'小明','2024/10/9','A'),(N'小明','2024/10/10',N'休'),(N'小明','2024/10/11','A'),(N'小明','2024/10/12',N'休')
,(N'小明','2024/10/13',N'休'),(N'小明','2024/10/14','B'),(N'小明','2024/10/15','B'),(N'小明','2024/10/16','B')
,(N'小明','2024/10/17','B'),(N'小明','2024/10/18','B'),(N'小明','2024/10/19',N'休'),(N'小明','2024/10/20',N'休')
,(N'小明','2024/10/21','A'),(N'小明','2024/10/22','A'),(N'小明','2024/10/23','A'),(N'小明','2024/10/24','A')
,(N'小明','2024/10/25','A'),(N'小明','2024/10/26',N'休'),(N'小明','2024/10/27',N'休'),(N'小明','2024/10/28','A')
,(N'小明','2024/10/29','A'),(N'小明','2024/10/30','A'),(N'小明','2024/10/31','A');
SELECT *
FROM da;
| Name | SetDate | Code |
|---|---|---|
| 大白 | 2024-09-01 | A |
| 大白 | 2024-09-02 | A |
| 大白 | 2024-09-03 | A |
| 大白 | 2024-09-04 | A |
| 大白 | 2024-09-05 | 休 |
| 大白 | 2024-09-06 | 休 |
| 大白 | 2024-09-07 | A |
| 大白 | 2024-09-08 | A |
| 大白 | 2024-09-09 | A |
| 大白 | 2024-09-10 | 休 |
| 大白 | 2024-09-11 | A |
| 大白 | 2024-09-12 | 休 |
| 大白 | 2024-09-13 | 休 |
| 大白 | 2024-09-14 | B |
| 大白 | 2024-09-15 | B |
| 大白 | 2024-09-16 | B |
| 大白 | 2024-09-17 | B |
| 大白 | 2024-09-18 | B |
| 大白 | 2024-09-19 | 休 |
| 大白 | 2024-09-20 | 休 |
| 大白 | 2024-09-21 | A |
| 大白 | 2024-09-22 | A |
| 大白 | 2024-09-23 | A |
| 大白 | 2024-09-24 | A |
| 大白 | 2024-09-25 | A |
| 大白 | 2024-09-26 | 休 |
| 大白 | 2024-09-27 | 休 |
| 大白 | 2024-09-28 | A |
| 大白 | 2024-09-29 | A |
| 大白 | 2024-09-30 | A |
| 大白 | 2024-10-01 | 特 |
| 大白 | 2024-10-02 | 特 |
| 大白 | 2024-10-03 | B |
| 大白 | 2024-10-04 | B |
| 大白 | 2024-10-05 | 休 |
| 大白 | 2024-10-06 | 休 |
| 大白 | 2024-10-07 | B |
| 大白 | 2024-10-08 | B |
| 大白 | 2024-10-09 | B |
| 大白 | 2024-10-10 | 休 |
| 大白 | 2024-10-11 | B |
| 大白 | 2024-10-12 | 休 |
| 大白 | 2024-10-13 | 休 |
| 大白 | 2024-10-14 | A |
| 大白 | 2024-10-15 | A |
| 大白 | 2024-10-16 | A |
| 大白 | 2024-10-17 | A |
| 大白 | 2024-10-18 | A |
| 大白 | 2024-10-19 | 休 |
| 大白 | 2024-10-20 | 休 |
| 大白 | 2024-10-21 | B |
| 大白 | 2024-10-22 | B |
| 大白 | 2024-10-23 | B |
| 大白 | 2024-10-24 | B |
| 大白 | 2024-10-25 | B |
| 大白 | 2024-10-26 | 休 |
| 大白 | 2024-10-27 | 休 |
| 大白 | 2024-10-28 | B |
| 大白 | 2024-10-29 | B |
| 大白 | 2024-10-30 | B |
| 大白 | 2024-10-31 | B |
| 小明 | 2024-10-01 | A |
| 小明 | 2024-10-02 | A |
| 小明 | 2024-10-03 | A |
| 小明 | 2024-10-04 | A |
| 小明 | 2024-10-05 | 休 |
| 小明 | 2024-10-06 | 休 |
| 小明 | 2024-10-07 | A |
| 小明 | 2024-10-08 | A |
| 小明 | 2024-10-09 | A |
| 小明 | 2024-10-10 | 休 |
| 小明 | 2024-10-11 | A |
| 小明 | 2024-10-12 | 休 |
| 小明 | 2024-10-13 | 休 |
| 小明 | 2024-10-14 | B |
| 小明 | 2024-10-15 | B |
| 小明 | 2024-10-16 | B |
| 小明 | 2024-10-17 | B |
| 小明 | 2024-10-18 | B |
| 小明 | 2024-10-19 | 休 |
| 小明 | 2024-10-20 | 休 |
| 小明 | 2024-10-21 | A |
| 小明 | 2024-10-22 | A |
| 小明 | 2024-10-23 | A |
| 小明 | 2024-10-24 | A |
| 小明 | 2024-10-25 | A |
| 小明 | 2024-10-26 | 休 |
| 小明 | 2024-10-27 | 休 |
| 小明 | 2024-10-28 | A |
| 小明 | 2024-10-29 | A |
| 小明 | 2024-10-30 | A |
| 小明 | 2024-10-31 | A |
-- 分割年月
WITH CTE00 AS (
SELECT Name,LEFT(SetDate,7) AS 'YM',SetDate,Code
FROM da),
-- 每月 1 日'休'、'特', 找到第一個班別向上補
CTE01 AS (
SELECT CTE00.Name,CTE00.YM,CTE00.SetDate,
CASE WHEN CTE00.SetDate < K.SetDate THEN K.Code ELSE CTE00.Code END AS Code
FROM CTE00
LEFT JOIN (
SELECT * FROM (
SELECT Row_Number() OVER (PARTITION BY Name,YM ORDER BY SetDate) AS RN,*
FROM CTE00
WHERE Code IN ('A','B')) AS K
WHERE RN =1
) AS K ON K.Name=CTE00.Name AND K.YM=CTE00.YM),
-- 向下補班別
CTE02 AS (
SELECT Name,SetDate,YM,
CASE WHEN Code NOT IN ('A','B') THEN NULL ELSE Code END AS CodeX
FROM CTE01),
--
CTE03 AS (
SELECT step_l.Name,step_l.YM,step_l.SetDate,step_2.CodeX as Code
FROM (
SELECT Name,YM,SetDate,CodeX,
SUM(CASE WHEN CodeX IS NULL THEN 0 ELSE 1 END) OVER (PARTITION BY Name,YM ORDER BY SetDate) as rank_id
FROM CTE02) as step_l
LEFT JOIN (
SELECT Name,YM,SetDate,CodeX,
Row_Number() OVER (PARTITION BY Name,YM ORDER BY SetDate) AS join_id
FROM CTE02 WHERE CodeX IS NOT NULL) as step_2
ON step_l.Name = step_2.Name
AND step_l.YM = step_2.YM
AND step_l.rank_id = step_2.join_id),
-- Code 內無'休'、'特'
| Name | YM | AllStr |
|---|---|---|
| 大白 | 2024-09 | A:01~13,B:14~20,A:21~30 |
| 大白 | 2024-10 | B:01~13,A:14~20,B:21~31 |
| 小明 | 2024-10 | A:01~13,B:14~20,A:21~31 |