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);

INSERT INTO WO (MECHANIC_NAME,ASSET_ID,STATE_NAME) VALUES
(N'A',N'1',N'已完修'),
(N'B',N'2',N'已完修'),
(N'C',N'3',N'已完修'),
(N'A',N'4',N'未完修'),
(N'B',N'5',N'已完修'),
(N'B',N'6',N'未完修'),
(N'C',N'7',N'已退修'),
(N'C',N'8',N'已退修'),
(N'C',N'9',N'已退修'),
(N'C',N'10',N'未完修'),
(N'C',N'11',N'未完修'),
(N'C',N'12',N'未完修'),
(N'C',N'13',N'未完修'),
(N'C',N'14',N'未完修'),
(N'D',N'15',N'未完修');

SELECT *
FROM WO;


MECHANIC_NAME
ASSET_ID
STATE_NAME


A
1
已完修

B
2
已完修

C
3
已完修

A
4
未完修

B
5
已完修

B
6
未完修

C
7
已退修

C
8
已退修

C
9
已退修

C
10
未完修

C
11
未完修

C
12
未完修

C
13
未完修

C
14
未完修

D
15
未完修


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

INSERT INTO ASSET (ASSET_ID,HAVEWARRANTY,BRAND_NAME) VALUES
(N'1',N'1',N'brother'),
(N'2',N'0',N'NCC'),
(N'3',N'1',N'X'),
(N'4',N'0',N'Y'),
(N'5',N'1',N'Y'),
(N'6',N'0',N'Z'),
(N'7',N'1',N'Z'),
(N'8',N'0',N'Z'),
(N'9',N'1',N'Z'),
(N'10',N'1',NULL),
(N'11',N'0',NULL),
(N'12',N'0',NULL),
(N'13',N'1',N'brother'),
(N'14',N'0',N'NCC');

SELECT *
FROM ASSET;


ASSET_ID
HAVEWARRANTY
BRAND_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


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
ASSET_ID
HAVEWARRANTY
BRAND_NAME


1
A
1
已完修
1
1
brother

2
A
4
未完修
4
0
Y

1
B
2
已完修
2
0
NCC

2
B
5
已完修
5
1
Y

3
B
6
未完修
6
0
Z

1
C
10
未完修
10
1
null

2
C
11
未完修
11
0
null

3
C
12
未完修
12
0
null

4
C
13
未完修
13
1
brother

5
C
14
未完修
14
0
NCC

6
C
3
已完修
3
1
X

7
C
7
已退修
7
1
Z

8
C
8
已退修
8
0
Z

9
C
9
已退修
9
1
Z

1
D
15
未完修
null
null
null


SELECT MECHANIC_NAME AS '維修人員',TOTAL AS '接單數',
HAVEWARRANTY_Y AS '保固內',
HAVEWARRANTY_N AS '保固外',
FIX_FINISH AS '已完修',
FIX_RETURN AS '已退修',
BRAND AS '它牌',
BRAND_2 AS '它牌(不含 NULL))',
CONVERT(VARCHAR,CONVERT(DECIMAL(6,2),100.00 * (FIX_FINISH * 1.00 + FIX_RETURN * 1.00) / TOTAL * 1.00)) + '%' AS '完成率'
FROM (
  SELECT A.MECHANIC_NAME,
  COUNT(*) AS 'TOTAL',
  SUM(CASE WHEN B.HAVEWARRANTY = '1' THEN 1 ELSE 0 END) AS 'HAVEWARRANTY_Y',
  SUM(CASE WHEN B.HAVEWARRANTY = '0' THEN 1 ELSE 0 END) AS 'HAVEWARRANTY_N',
  SUM(CASE WHEN A.STATE_NAME like N'%已完修%' THEN 1 ELSE 0 END) AS 'FIX_FINISH',
  SUM(CASE WHEN A.STATE_NAME like N'%已退修%' THEN 1 ELSE 0 END) AS 'FIX_RETURN',
  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 'BRAND',
  SUM(CASE WHEN B.BRAND_NAME NOT IN ('brother', 'NCC') THEN 1 ELSE 0 END) AS 'BRAND_2'
  FROM WO A
  LEFT JOIN ASSET B ON B.ASSET_ID = A.ASSET_ID
  GROUP BY A.MECHANIC_NAME
) AS C


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


A
2
1
1
1
0
1
1
50.00%

B
3
1
2
2
0
2
2
66.67%

C
9
5
4
1
3
7
4
44.44%

D
1
0
0
0
0
0
0
0.00%

MECHANIC_NAME	ASSET_ID	STATE_NAME
A	1	已完修
B	2	已完修
C	3	已完修
A	4	未完修
B	5	已完修
B	6	未完修
C	7	已退修
C	8	已退修
C	9	已退修
C	10	未完修
C	11	未完修
C	12	未完修
C	13	未完修
C	14	未完修
D	15	未完修

ASSET_ID	HAVEWARRANTY	BRAND_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