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,
REGISTERDATE DATE,
CLOSEDDATE DATE);

INSERT INTO WO (MECHANIC_NAME,ASSET_ID,STATE_NAME,REGISTERDATE,CLOSEDDATE) VALUES
(N'A',N'1',N'已完修','20220601','20220602'),
(N'B',N'2',N'已完修','20220602','20220603'),
(N'C',N'3',N'已完修','20220603','20220604'),
(N'A',N'4',N'未完修','20220604',NULL),
(N'B',N'5',N'已完修','20220605','20220606'),
(N'B',N'6',N'未完修','20220606',NULL),
(N'C',N'7',N'已退修','20220607','20220608'),
(N'C',N'8',N'已退修','20220608','20220609'),
(N'C',N'9',N'已退修','20220609','20220610'),
(N'C',N'10',N'未完修','20220610',NULL),
(N'C',N'11',N'未完修','20220611',NULL),
(N'C',N'12',N'已完修','20220612','20220613'),
(N'C',N'13',N'已完修','20220613','20220619'),
(N'C',N'14',N'已完修','20220614','20220711'),
(N'D',N'15',N'已完修','20220615','20220712');

SELECT *
FROM WO;


MECHANIC_NAME
ASSET_ID
STATE_NAME
REGISTERDATE
CLOSEDDATE


A
1
已完修
2022-06-01
2022-06-02

B
2
已完修
2022-06-02
2022-06-03

C
3
已完修
2022-06-03
2022-06-04

A
4
未完修
2022-06-04
null

B
5
已完修
2022-06-05
2022-06-06

B
6
未完修
2022-06-06
null

C
7
已退修
2022-06-07
2022-06-08

C
8
已退修
2022-06-08
2022-06-09

C
9
已退修
2022-06-09
2022-06-10

C
10
未完修
2022-06-10
null

C
11
未完修
2022-06-11
null

C
12
已完修
2022-06-12
2022-06-13

C
13
已完修
2022-06-13
2022-06-19

C
14
已完修
2022-06-14
2022-07-11

D
15
已完修
2022-06-15
2022-07-12


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',*,
CASE WHEN A.CLOSEDDATE <= DATEADD(day, 5, A.REGISTERDATE) THEN 1 ELSE 0 END AS 'DAY_TEMP'
FROM WO A
LEFT JOIN ASSET B ON B.ASSET_ID = A.ASSET_ID
WHERE A.REGISTERDATE >='20220601' AND A.REGISTERDATE <='20220614'
ORDER BY A.MECHANIC_NAME,A.ASSET_ID


PART NO
MECHANIC_NAME
ASSET_ID
STATE_NAME
REGISTERDATE
CLOSEDDATE
ASSET_ID
HAVEWARRANTY
BRAND_NAME
DAY_TEMP


1
A
1
已完修
2022-06-01
2022-06-02
1
1
brother
1

2
A
4
未完修
2022-06-04
null
4
0
Y
0

1
B
2
已完修
2022-06-02
2022-06-03
2
0
NCC
1

2
B
5
已完修
2022-06-05
2022-06-06
5
1
Y
1

3
B
6
未完修
2022-06-06
null
6
0
Z
0

1
C
10
未完修
2022-06-10
null
10
1
null
0

2
C
11
未完修
2022-06-11
null
11
0
null
0

3
C
12
已完修
2022-06-12
2022-06-13
12
0
null
1

4
C
13
已完修
2022-06-13
2022-06-19
13
1
brother
0

5
C
14
已完修
2022-06-14
2022-07-11
14
0
NCC
0

6
C
3
已完修
2022-06-03
2022-06-04
3
1
X
1

7
C
7
已退修
2022-06-07
2022-06-08
7
1
Z
1

8
C
8
已退修
2022-06-08
2022-06-09
8
0
Z
1

9
C
9
已退修
2022-06-09
2022-06-10
9
1
Z
1


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 '完成率',
DAY_EFFECTIVE AS '達成數',
CONVERT(VARCHAR,CONVERT(DECIMAL(6,2),100.00 * DAY_EFFECTIVE / 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',
  SUM(CASE WHEN A.CLOSEDDATE <= DATEADD(day, 5, A.REGISTERDATE) THEN 1 ELSE 0 END) AS 'DAY_EFFECTIVE'
  FROM WO A
  LEFT JOIN ASSET B ON B.ASSET_ID = A.ASSET_ID
  WHERE A.REGISTERDATE >='20220601' AND A.REGISTERDATE <='20220614'
  GROUP BY A.MECHANIC_NAME
) AS C


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


A
2
1
1
1
0
1
1
50.00%
1
50.00%

B
3
1
2
2
0
2
2
66.67%
2
66.67%

C
9
5
4
4
3
7
4
77.78%
5
55.56%

MECHANIC_NAME	ASSET_ID	STATE_NAME	REGISTERDATE	CLOSEDDATE
A	1	已完修	2022-06-01	2022-06-02
B	2	已完修	2022-06-02	2022-06-03
C	3	已完修	2022-06-03	2022-06-04
A	4	未完修	2022-06-04	null
B	5	已完修	2022-06-05	2022-06-06
B	6	未完修	2022-06-06	null
C	7	已退修	2022-06-07	2022-06-08
C	8	已退修	2022-06-08	2022-06-09
C	9	已退修	2022-06-09	2022-06-10
C	10	未完修	2022-06-10	null
C	11	未完修	2022-06-11	null
C	12	已完修	2022-06-12	2022-06-13
C	13	已完修	2022-06-13	2022-06-19
C	14	已完修	2022-06-14	2022-07-11
D	15	已完修	2022-06-15	2022-07-12

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

維修人員	接單數	保固內	保固外	已完修	已退修	它牌	它牌(不含 NULL))	完成率	達成數	達成率
A	2	1	1	1	0	1	1	50.00%	1	50.00%
B	3	1	2	2	0	2	2	66.67%	2	66.67%
C	9	5	4	4	3	7	4	77.78%	5	55.56%