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'自行送件','2022/5/5'),
(N'B',N'2',N'已完修',N'自行寄件','2022/5/5'),
(N'C',N'3',N'已完修',N'派員收件','2022/5/5'),
(N'A',N'4',N'未完修',N'到府維修','2022/5/5'),
(N'B',N'5',N'已完修',N'自行送件','2022/5/5'),
(N'B',N'6',N'未完修',N'自行寄件',null),
(N'C',N'7',N'已完修',N'派員收件','2022/5/5'),
(N'C',N'8',N'已完修',N'到府維修','2022/5/5'),
(N'C',N'9',N'未完修',N'自行送件','2022/5/5'),
(N'C',N'10',N'未完修',N'自行寄件',null),
(N'C',N'11',N'未完修',N'派員收件','2022/5/5'),
(N'C',N'12',N'未完修',N'到府維修','2022/5/5'),
(N'C',N'13',N'未完修',N'自行送件','2022/5/5'),
(N'C',N'14',N'未完修',N'自行寄件','2022/5/5'),
(N'D',N'15',N'未完修',N'派員收件','2022/5/5');

SELECT *
FROM WO;


MECHANIC_NAME
ASSET_ID
STATE_NAME
SENDCHOICE_NAME
RECEIVEDATE


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

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

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

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

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

B
6
未完修
自行寄件
null

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

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

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

C
10
未完修
自行寄件
null

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

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

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

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

D
15
未完修
派員收件
2022-05-05 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
,RECEIVEDATE datetime
);

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'教學機');

SELECT *
FROM ASSET;


ASSET_ID
HAVEWARRANTY
BRAND_NAME
MACHINEUSE_NAME
RECEIVEDATE


1
1
brother
展示機
null

2
0
NCC
教學機
null

3
1
X
活動用
null

4
0
Y
整理用
null

5
1
Y
展示機
null

6
0
Z
教學機
null

7
1
Z
活動用
null

8
0
Z
整理用
null

9
1
Z
展示機
null

10
1
null
教學機
null

11
0
null
活動用
null

12
0
null
整理用
null

13
1
brother
展示機
null

14
0
NCC
教學機
null


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
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
RECEIVEDATE


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

2
A
4
未完修
到府維修
2022-05-05 00:00:00.000
4
0
Y
整理用
null

1
B
2
已完修
自行寄件
2022-05-05 00:00:00.000
2
0
NCC
教學機
null

2
B
5
已完修
自行送件
2022-05-05 00:00:00.000
5
1
Y
展示機
null

3
B
6
未完修
自行寄件
null
6
0
Z
教學機
null

1
C
10
未完修
自行寄件
null
10
1
null
教學機
null

2
C
11
未完修
派員收件
2022-05-05 00:00:00.000
11
0
null
活動用
null

3
C
12
未完修
到府維修
2022-05-05 00:00:00.000
12
0
null
整理用
null

4
C
13
未完修
自行送件
2022-05-05 00:00:00.000
13
1
brother
展示機
null

5
C
14
未完修
自行寄件
2022-05-05 00:00:00.000
14
0
NCC
教學機
null

6
C
3
已完修
派員收件
2022-05-05 00:00:00.000
3
1
X
活動用
null

7
C
7
已完修
派員收件
2022-05-05 00:00:00.000
7
1
Z
活動用
null

8
C
8
已完修
到府維修
2022-05-05 00:00:00.000
8
0
Z
整理用
null

9
C
9
未完修
自行送件
2022-05-05 00:00:00.000
9
1
Z
展示機
null

1
D
15
未完修
派員收件
2022-05-05 00:00:00.000
null
null
null
null
null


SELECT A.MECHANIC_NAME AS '維修人員',
COUNT(*) AS '接單數',
SUM(CASE WHEN B.HAVEWARRANTY = '1' THEN 1 ELSE 0 END) AS '保固內',
SUM(CASE WHEN B.HAVEWARRANTY = '0' THEN 1 ELSE 0 END) AS '保固外',
SUM(CASE WHEN A.STATE_NAME like N'%已完修%' THEN 1 ELSE 0 END) AS '已完修',
SUM(CASE WHEN B.ASSET_ID IS NOT NULL 
AND ISNULL(B.BRAND_NAME,'BRAND NAME IS NULL') NOT IN ('brother', 'NCC') THEN 1 ELSE 0 END) AS '它牌',
SUM(CASE WHEN B.BRAND_NAME NOT IN ('brother', 'NCC') THEN 1 ELSE 0 END) AS '它牌(不含 NULL))'
FROM WO A
LEFT JOIN ASSET B ON B.ASSET_ID = A.ASSET_ID
GROUP BY A.MECHANIC_NAME


維修人員
接單數
保固內
保固外
已完修
它牌
它牌(不含 NULL))


A
2
1
1
1
1
1

B
3
1
2
2
2
2

C
9
5
4
3
7
4

D
1
0
0
0
0
0

MECHANIC_NAME	ASSET_ID	STATE_NAME	SENDCHOICE_NAME	RECEIVEDATE
A	1	已完修	自行送件	2022-05-05 00:00:00.000
B	2	已完修	自行寄件	2022-05-05 00:00:00.000
C	3	已完修	派員收件	2022-05-05 00:00:00.000
A	4	未完修	到府維修	2022-05-05 00:00:00.000
B	5	已完修	自行送件	2022-05-05 00:00:00.000
B	6	未完修	自行寄件	null
C	7	已完修	派員收件	2022-05-05 00:00:00.000
C	8	已完修	到府維修	2022-05-05 00:00:00.000
C	9	未完修	自行送件	2022-05-05 00:00:00.000
C	10	未完修	自行寄件	null
C	11	未完修	派員收件	2022-05-05 00:00:00.000
C	12	未完修	到府維修	2022-05-05 00:00:00.000
C	13	未完修	自行送件	2022-05-05 00:00:00.000
C	14	未完修	自行寄件	2022-05-05 00:00:00.000
D	15	未完修	派員收件	2022-05-05 00:00:00.000

ASSET_ID	HAVEWARRANTY	BRAND_NAME	MACHINEUSE_NAME	RECEIVEDATE
1	1	brother	展示機	null
2	0	NCC	教學機	null
3	1	X	活動用	null
4	0	Y	整理用	null
5	1	Y	展示機	null
6	0	Z	教學機	null
7	1	Z	活動用	null
8	0	Z	整理用	null
9	1	Z	展示機	null
10	1	null	教學機	null
11	0	null	活動用	null
12	0	null	整理用	null
13	1	brother	展示機	null
14	0	NCC	教學機	null