SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE WO (
MECHANIC_NAME NVARCHAR(50),
ASSET_ID NVARCHAR(50) NULL,
STATE_NAME NVARCHAR(50) NULL,
SENDCHOICE_NAME NVARCHAR(50) NULL,
RECEIVEDATE DATETIME);

INSERT INTO WO (MECHANIC_NAME,ASSET_ID,STATE_NAME,SENDCHOICE_NAME,RECEIVEDATE) VALUES
(N'A',N'1',N'已完修',N'自行送件','20220601'),          
(N'B',N'2',N'已完修',N'自行寄件','20220602'),          
(N'C',N'3',N'已完修',N'派員收件','20220603'),          
(N'A',N'4',N'未完修',N'到府維修','20220604'),          
(N'B',N'5',N'已完修',N'自行送件','20220605'),          
(N'B',N'6',N'未完修',N'自行寄件','20220606'),          
(N'C',N'7',N'已完修',N'派員收件','20220607'),          
(N'C',N'8',N'已完修',N'到府維修','20220608'),          
(N'C',N'9',N'未完修',N'自行送件','20220609'),          
(N'C',N'10',N'未完修',N'自行寄件','20220610'),     
(N'C',N'11',N'未完修',N'派員收件','20220611'),     
(N'C',N'12',N'未完修',N'到府維修','20220612'),     
(N'C',N'13',N'未完修',N'自行送件','20220613'),     
(N'C',N'14',N'未完修',N'自行寄件','20220614'),     
(N'D',N'15',N'未完修',N'派員收件','20220615'),
--
(N'A',N'21',N'已完修',N'自行送件','20220701'),      
(N'A',N'22',N'已完修',N'自行寄件','20220702'),      
(N'A',N'23',N'已完修',N'派員收件','20220703'),      
(N'A',N'24',N'未完修',N'到府維修','20220704'),      
(N'A',N'25',N'已完修',N'自行送件','20220705'),      
(N'B',N'26',N'未完修',N'自行寄件','20220706'),      
(N'B',N'27',N'已完修',N'派員收件','20220707'),      
(N'B',N'28',N'已完修',N'到府維修','20220708'),      
(N'B',N'29',N'未完修',N'自行送件','20220709'),      
(N'B',N'30',N'未完修',N'自行寄件','20220710'),     
(N'C',N'31',N'未完修',N'派員收件','20220711'),     
(N'C',N'32',N'未完修',N'到府維修','20220712'),     
(N'C',N'33',N'未完修',N'自行送件','20220713'),     
(N'C',N'34',N'未完修',N'自行寄件','20220714'),     
(N'C',N'35',N'未完修',N'派員收件','20220715');

SELECT *
FROM WO;


MECHANIC_NAME
ASSET_ID
STATE_NAME
SENDCHOICE_NAME
RECEIVEDATE


A
1
已完修
自行送件
2022-06-01 00:00:00.000

B
2
已完修
自行寄件
2022-06-02 00:00:00.000

C
3
已完修
派員收件
2022-06-03 00:00:00.000

A
4
未完修
到府維修
2022-06-04 00:00:00.000

B
5
已完修
自行送件
2022-06-05 00:00:00.000

B
6
未完修
自行寄件
2022-06-06 00:00:00.000

C
7
已完修
派員收件
2022-06-07 00:00:00.000

C
8
已完修
到府維修
2022-06-08 00:00:00.000

C
9
未完修
自行送件
2022-06-09 00:00:00.000

C
10
未完修
自行寄件
2022-06-10 00:00:00.000

C
11
未完修
派員收件
2022-06-11 00:00:00.000

C
12
未完修
到府維修
2022-06-12 00:00:00.000

C
13
未完修
自行送件
2022-06-13 00:00:00.000

C
14
未完修
自行寄件
2022-06-14 00:00:00.000

D
15
未完修
派員收件
2022-06-15 00:00:00.000

A
21
已完修
自行送件
2022-07-01 00:00:00.000

A
22
已完修
自行寄件
2022-07-02 00:00:00.000

A
23
已完修
派員收件
2022-07-03 00:00:00.000

A
24
未完修
到府維修
2022-07-04 00:00:00.000

A
25
已完修
自行送件
2022-07-05 00:00:00.000

B
26
未完修
自行寄件
2022-07-06 00:00:00.000

B
27
已完修
派員收件
2022-07-07 00:00:00.000

B
28
已完修
到府維修
2022-07-08 00:00:00.000

B
29
未完修
自行送件
2022-07-09 00:00:00.000

B
30
未完修
自行寄件
2022-07-10 00:00:00.000

C
31
未完修
派員收件
2022-07-11 00:00:00.000

C
32
未完修
到府維修
2022-07-12 00:00:00.000

C
33
未完修
自行送件
2022-07-13 00:00:00.000

C
34
未完修
自行寄件
2022-07-14 00:00:00.000

C
35
未完修
派員收件
2022-07-15 00:00:00.000


CREATE TABLE ASSET (
ASSET_ID NVARCHAR(50),
HAVEWARRANTY NVARCHAR(50) NULL,
BRAND_NAME NVARCHAR(50) NULL,
MACHINEUSE_NAME NVARCHAR(50) NULL);

INSERT INTO ASSET (ASSET_ID,HAVEWARRANTY,BRAND_NAME,MACHINEUSE_NAME) VALUES
(N'1',N'1',N'brother',N'展示機'),
(N'2',N'0',N'NCC',N'教學機'),
(N'3',N'1',N'X',N'活動用'),
(N'4',N'0',N'Y',N'整理用'),
(N'5',N'1',N'Y',N'展示機'),
(N'6',N'0',N'Z',N'教學機'),
(N'7',N'1',N'Z',N'活動用'),
(N'8',N'0',N'Z',N'整理用'),
(N'9',N'1',N'Z',N'展示機'),
(N'10',N'1',NULL,N'教學機'),
(N'11',N'0',NULL,N'活動用'),
(N'12',N'0',NULL,N'整理用'),
(N'13',N'1',N'brother',N'展示機'),
(N'14',N'0',N'NCC',N'教學機'),
--
(N'21',N'1',N'brother',N'展示機'),
(N'22',N'1',N'NCC',N'教學機'),
(N'23',N'1',N'X',N'活動用'),
(N'24',N'1',N'Y',N'整理用'),
(N'25',N'1',N'Y',N'展示機'),
(N'26',N'1',N'Z',N'教學機'),
(N'27',N'1',N'Z',N'活動用'),
(N'28',N'1',N'Z',N'整理用'),
(N'29',N'1',N'Z',N'展示機'),
(N'30',N'1',NULL,N'教學機'),
(N'31',N'1',NULL,N'活動用'),
(N'32',N'1',NULL,N'整理用'),
(N'33',N'1',N'brother',N'展示機'),
(N'34',N'1',N'NCC',N'教學機');

SELECT *
FROM ASSET;


ASSET_ID
HAVEWARRANTY
BRAND_NAME
MACHINEUSE_NAME


1
1
brother
展示機

2
0
NCC
教學機

3
1
X
活動用

4
0
Y
整理用

5
1
Y
展示機

6
0
Z
教學機

7
1
Z
活動用

8
0
Z
整理用

9
1
Z
展示機

10
1
null
教學機

11
0
null
活動用

12
0
null
整理用

13
1
brother
展示機

14
0
NCC
教學機

21
1
brother
展示機

22
1
NCC
教學機

23
1
X
活動用

24
1
Y
整理用

25
1
Y
展示機

26
1
Z
教學機

27
1
Z
活動用

28
1
Z
整理用

29
1
Z
展示機

30
1
null
教學機

31
1
null
活動用

32
1
null
整理用

33
1
brother
展示機

34
1
NCC
教學機


SELECT ROW_NUMBER() OVER (PARTITION BY A.MECHANIC_NAME ORDER BY A.MECHANIC_NAME) AS 'PART NO',*
FROM WO A
LEFT JOIN ASSET B ON B.ASSET_ID = A.ASSET_ID
WHERE RECEIVEDATE >='20220101' AND RECEIVEDATE < '20230101'
AND MECHANIC_NAME = 'A'
ORDER BY A.MECHANIC_NAME,A.ASSET_ID


PART NO
MECHANIC_NAME
ASSET_ID
STATE_NAME
SENDCHOICE_NAME
RECEIVEDATE
ASSET_ID
HAVEWARRANTY
BRAND_NAME
MACHINEUSE_NAME


1
A
1
已完修
自行送件
2022-06-01 00:00:00.000
1
1
brother
展示機

2
A
21
已完修
自行送件
2022-07-01 00:00:00.000
21
1
brother
展示機

3
A
22
已完修
自行寄件
2022-07-02 00:00:00.000
22
1
NCC
教學機

4
A
23
已完修
派員收件
2022-07-03 00:00:00.000
23
1
X
活動用

5
A
24
未完修
到府維修
2022-07-04 00:00:00.000
24
1
Y
整理用

6
A
25
已完修
自行送件
2022-07-05 00:00:00.000
25
1
Y
展示機

7
A
4
未完修
到府維修
2022-06-04 00:00:00.000
4
0
Y
整理用


;WITH CTE_XX AS (
  SELECT A.*,B.HAVEWARRANTY,B.BRAND_NAME,B.MACHINEUSE_NAME,DATEPART(MONTH, RECEIVEDATE) AS 'RECEIVEDATE_M'
  FROM WO A
  LEFT JOIN ASSET B ON B.ASSET_ID = A.ASSET_ID
  WHERE RECEIVEDATE >='20220101' AND RECEIVEDATE < '20230101'
  AND MECHANIC_NAME = 'A'),
CTE_01 AS (
  -- 到府維修,自行送件,派員收件
  SELECT  MECHANIC_NAME,RECEIVEDATE_M,SENDCHOICE_NAME AS 'ITEM',COUNT(1) AS 'COUNTER',
  1 AS 'GROUPNO'
  FROM CTE_XX
  WHERE SENDCHOICE_NAME IN (N'到府維修',N'自行送件',N'派員收件')
  GROUP BY MECHANIC_NAME,RECEIVEDATE_M,SENDCHOICE_NAME),
CTE_02 AS (
  -- 保內
  SELECT  MECHANIC_NAME,RECEIVEDATE_M,N'保固內' AS 'ITEM',
  SUM(CASE WHEN HAVEWARRANTY = '1' THEN 1 ELSE 0 END) AS 'COUNTER',
  2 AS 'GROUPNO'
  FROM CTE_XX
  GROUP BY MECHANIC_NAME,RECEIVEDATE_M),
CTE_03 AS (
  -- 保外
  SELECT  MECHANIC_NAME,RECEIVEDATE_M,N'保固外' AS 'ITEM',
  SUM(CASE WHEN HAVEWARRANTY = '0' THEN 1 ELSE 0 END) AS 'COUNTER',
  2 AS 'GROUPNO'
  FROM CTE_XX
  GROUP BY MECHANIC_NAME,RECEIVEDATE_M),
CTE_04 AS (
  -- 活動用,展示機,教學機
  SELECT  MECHANIC_NAME,RECEIVEDATE_M,MACHINEUSE_NAME AS 'ITEM',COUNT(1) AS 'COUNTER',
  3 AS 'GROUPNO'
  FROM CTE_XX
  WHERE MACHINEUSE_NAME IN (N'活動用',N'展示機',N'教學機')
  GROUP BY MECHANIC_NAME,RECEIVEDATE_M,MACHINEUSE_NAME),
CTE_05 AS (
  -- 整理用
  SELECT  MECHANIC_NAME,RECEIVEDATE_M,MACHINEUSE_NAME AS 'ITEM',COUNT(1) AS 'COUNTER',
  3 AS 'GROUPNO'
  FROM CTE_XX
  WHERE MACHINEUSE_NAME IN (N'整理用')
  GROUP BY MECHANIC_NAME,RECEIVEDATE_M,MACHINEUSE_NAME),
CTE_06 AS (
  SELECT *
  FROM CTE_01
  UNION ALL
  SELECT *
  FROM CTE_02
  UNION ALL
  SELECT *
  FROM CTE_03
  UNION ALL
  SELECT *
  FROM CTE_04
  UNION ALL
  SELECT *
  FROM CTE_05),
CTE_KK AS (
  SELECT MECHANIC_NAME,GROUPNO,ITEM,
  SUM(COUNTER) AS 'TOTAL',
  SUM(CASE WHEN RECEIVEDATE_M='1' THEN COUNTER ELSE 0 END) AS 'M01',
  SUM(CASE WHEN RECEIVEDATE_M='2' THEN COUNTER ELSE 0 END) AS 'M02',
  SUM(CASE WHEN RECEIVEDATE_M='3' THEN COUNTER ELSE 0 END) AS 'M03',
  SUM(CASE WHEN RECEIVEDATE_M='4' THEN COUNTER ELSE 0 END) AS 'M04',
  SUM(CASE WHEN RECEIVEDATE_M='5' THEN COUNTER ELSE 0 END) AS 'M05',
  SUM(CASE WHEN RECEIVEDATE_M='6' THEN COUNTER ELSE 0 END) AS 'M06',
  SUM(CASE WHEN RECEIVEDATE_M='7' THEN COUNTER ELSE 0 END) AS 'M07',
  SUM(CASE WHEN RECEIVEDATE_M='8' THEN COUNTER ELSE 0 END) AS 'M08',
  SUM(CASE WHEN RECEIVEDATE_M='9' THEN COUNTER ELSE 0 END) AS 'M09',
  SUM(CASE WHEN RECEIVEDATE_M='10' THEN COUNTER ELSE 0 END) AS 'M10',
  SUM(CASE WHEN RECEIVEDATE_M='11' THEN COUNTER ELSE 0 END) AS 'M11',
  SUM(CASE WHEN RECEIVEDATE_M='12' THEN COUNTER ELSE 0 END) AS 'M12'
  FROM CTE_06
  GROUP BY MECHANIC_NAME,GROUPNO,ITEM)

SELECT *
FROM (
  -- FOR GROUPNO = 1
    SELECT *
    FROM CTE_KK
    WHERE GROUPNO = 1
    UNION ALL
    SELECT MECHANIC_NAME,GROUPNO,N'小計 : GROUPNO = 1' AS ITEM,SUM(TOTAL) TOTAL,
    SUM(M01) AS M01,SUM(M02) AS M02,SUM(M03) AS M03,SUM(M04) AS M04,SUM(M05) AS M05,SUM(M06) AS M06,
    SUM(M07) AS M07,SUM(M08) AS M08,SUM(M09) AS M09,SUM(M10) AS M10,SUM(M11) AS M11,SUM(M12) AS M12
    FROM CTE_KK
    WHERE GROUPNO = 1
    GROUP BY MECHANIC_NAME,GROUPNO
  -- FOR GROUPNO = 2
    UNION ALL
    SELECT *
    FROM CTE_KK
    WHERE GROUPNO = 2
    UNION ALL
    SELECT MECHANIC_NAME,GROUPNO,N'小計 : GROUPNO = 2' AS ITEM,SUM(TOTAL) TOTAL,
    SUM(M01) AS M01,SUM(M02) AS M02,SUM(M03) AS M03,SUM(M04) AS M04,SUM(M05) AS M05,SUM(M06) AS M06,
    SUM(M07) AS M07,SUM(M08) AS M08,SUM(M09) AS M09,SUM(M10) AS M10,SUM(M11) AS M11,SUM(M12) AS M12
    FROM CTE_KK
    WHERE GROUPNO = 2
    GROUP BY MECHANIC_NAME,GROUPNO
  -- FOR GROUPNO = 3
    UNION ALL
    SELECT *
    FROM CTE_KK
    WHERE GROUPNO = 3
    AND ITEM IN (N'活動用',N'展示機',N'教學機')
    UNION ALL
    SELECT *
    FROM CTE_KK
    WHERE GROUPNO = 3
    AND ITEM IN (N'整理用')
    UNION ALL
    SELECT MECHANIC_NAME,GROUPNO,N'小計 : GROUPNO = 3 (不含整理用)' AS ITEM,SUM(TOTAL) TOTAL,
    SUM(M01) AS M01,SUM(M02) AS M02,SUM(M03) AS M03,SUM(M04) AS M04,SUM(M05) AS M05,SUM(M06) AS M06,
    SUM(M07) AS M07,SUM(M08) AS M08,SUM(M09) AS M09,SUM(M10) AS M10,SUM(M11) AS M11,SUM(M12) AS M12
    FROM CTE_KK
    WHERE GROUPNO = 3
    AND ITEM IN (N'活動用',N'展示機',N'教學機')
    GROUP BY MECHANIC_NAME,GROUPNO
    UNION ALL
    SELECT MECHANIC_NAME,GROUPNO,N'合計 : GROUPNO = 3' AS ITEM,SUM(TOTAL) TOTAL,
    SUM(M01) AS M01,SUM(M02) AS M02,SUM(M03) AS M03,SUM(M04) AS M04,SUM(M05) AS M05,SUM(M06) AS M06,
    SUM(M07) AS M07,SUM(M08) AS M08,SUM(M09) AS M09,SUM(M10) AS M10,SUM(M11) AS M11,SUM(M12) AS M12
    FROM CTE_KK
    WHERE GROUPNO = 3
    GROUP BY MECHANIC_NAME,GROUPNO
) AS A
ORDER BY A.MECHANIC_NAME,A.GROUPNO,
CASE WHEN LEFT(A.ITEM,4) = N'合計' THEN 99 
WHEN LEFT(A.ITEM,4) = N'小計' THEN 98 ELSE 1 END


MECHANIC_NAME
GROUPNO
ITEM
TOTAL
M01
M02
M03
M04
M05
M06
M07
M08
M09
M10
M11
M12


A
1
到府維修
2
0
0
0
0
0
1
1
0
0
0
0
0

A
1
派員收件
1
0
0
0
0
0
0
1
0
0
0
0
0

A
1
自行送件
3
0
0
0
0
0
1
2
0
0
0
0
0

A
1
小計 : GROUPNO = 1
6
0
0
0
0
0
2
4
0
0
0
0
0

A
2
保固內
6
0
0
0
0
0
1
5
0
0
0
0
0

A
2
保固外
1
0
0
0
0
0
1
0
0
0
0
0
0

A
2
小計 : GROUPNO = 2
7
0
0
0
0
0
2
5
0
0
0
0
0

A
3
展示機
3
0
0
0
0
0
1
2
0
0
0
0
0

A
3
教學機
1
0
0
0
0
0
0
1
0
0
0
0
0

A
3
活動用
1
0
0
0
0
0
0
1
0
0
0
0
0

A
3
整理用
2
0
0
0
0
0
1
1
0
0
0
0
0

A
3
小計 : GROUPNO = 3 (不含整理用)
5
0
0
0
0
0
1
4
0
0
0
0
0

A
3
合計 : GROUPNO = 3
7
0
0
0
0
0
2
5
0
0
0
0
0

MECHANIC_NAME	ASSET_ID	STATE_NAME	SENDCHOICE_NAME	RECEIVEDATE
A	1	已完修	自行送件	2022-06-01 00:00:00.000
B	2	已完修	自行寄件	2022-06-02 00:00:00.000
C	3	已完修	派員收件	2022-06-03 00:00:00.000
A	4	未完修	到府維修	2022-06-04 00:00:00.000
B	5	已完修	自行送件	2022-06-05 00:00:00.000
B	6	未完修	自行寄件	2022-06-06 00:00:00.000
C	7	已完修	派員收件	2022-06-07 00:00:00.000
C	8	已完修	到府維修	2022-06-08 00:00:00.000
C	9	未完修	自行送件	2022-06-09 00:00:00.000
C	10	未完修	自行寄件	2022-06-10 00:00:00.000
C	11	未完修	派員收件	2022-06-11 00:00:00.000
C	12	未完修	到府維修	2022-06-12 00:00:00.000
C	13	未完修	自行送件	2022-06-13 00:00:00.000
C	14	未完修	自行寄件	2022-06-14 00:00:00.000
D	15	未完修	派員收件	2022-06-15 00:00:00.000
A	21	已完修	自行送件	2022-07-01 00:00:00.000
A	22	已完修	自行寄件	2022-07-02 00:00:00.000
A	23	已完修	派員收件	2022-07-03 00:00:00.000
A	24	未完修	到府維修	2022-07-04 00:00:00.000
A	25	已完修	自行送件	2022-07-05 00:00:00.000
B	26	未完修	自行寄件	2022-07-06 00:00:00.000
B	27	已完修	派員收件	2022-07-07 00:00:00.000
B	28	已完修	到府維修	2022-07-08 00:00:00.000
B	29	未完修	自行送件	2022-07-09 00:00:00.000
B	30	未完修	自行寄件	2022-07-10 00:00:00.000
C	31	未完修	派員收件	2022-07-11 00:00:00.000
C	32	未完修	到府維修	2022-07-12 00:00:00.000
C	33	未完修	自行送件	2022-07-13 00:00:00.000
C	34	未完修	自行寄件	2022-07-14 00:00:00.000
C	35	未完修	派員收件	2022-07-15 00:00:00.000

ASSET_ID	HAVEWARRANTY	BRAND_NAME	MACHINEUSE_NAME
1	1	brother	展示機
2	0	NCC	教學機
3	1	X	活動用
4	0	Y	整理用
5	1	Y	展示機
6	0	Z	教學機
7	1	Z	活動用
8	0	Z	整理用
9	1	Z	展示機
10	1	null	教學機
11	0	null	活動用
12	0	null	整理用
13	1	brother	展示機
14	0	NCC	教學機
21	1	brother	展示機
22	1	NCC	教學機
23	1	X	活動用
24	1	Y	整理用
25	1	Y	展示機
26	1	Z	教學機
27	1	Z	活動用
28	1	Z	整理用
29	1	Z	展示機
30	1	null	教學機
31	1	null	活動用
32	1	null	整理用
33	1	brother	展示機
34	1	NCC	教學機