SQL Server 2017

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

CREATE TABLE device
	( [預算邊號] varchar(7), [BPM單號] varchar(20),[型號] varchar(20),[可承租數量] int, [啟用狀態] varchar(2));
INSERT INTO device
	([預算邊號], [BPM單號],[型號],[可承租數量],[啟用狀態])
VALUES
('107091', '1071101001','IBM',1, '1'),
('107100', '1071101002','IBM',2, '1'),
('107104', '1071101003','IBM',3, '1'),
('107105', '1071101004','IBM',4, '1'),
('107106', '1071101005','IBM',5, '1'),
('107107', '1071101006','IBM',6, '1'),
('107180', '1071101007','IBM',7, '1'),
('107091', '1071101011','VIEWSONIC',1, '1'),
('107100', '1071101012','VIEWSONIC',2, '1'),
('107104', '1071101013','VIEWSONIC',3, '1'),
('107105', '1071101014','VIEWSONIC',4, '1'),
('107106', '1071101015','VIEWSONIC',5, '1'),
('107107', '1071101016','VIEWSONIC',6, '1'),
('107180', '1071101017','VIEWSONIC',7, '1'),
('107091', '1071101021','NBASUS',1, '1'),
('107100', '1071101022','NBASUS',2, '1'),
('107104', '1071101023','NBASUS',3, '1'),
('107105', '1071101024','NBASUS',4, '1'),
('107106', '1071101025','NBASUS',5, '1'),
('107107', '1071101026','NBASUS',6, '1'),
('107180', '1071101027','NBASUS',7, '1');
21 rows affected

select SUBSTRING(a.預算邊號,4,3) as 預算部門,* 
from device as a


預算部門
預算邊號
BPM單號
型號
可承租數量
啟用狀態


091
107091
1071101001
IBM
1
1

100
107100
1071101002
IBM
2
1

104
107104
1071101003
IBM
3
1

105
107105
1071101004
IBM
4
1

106
107106
1071101005
IBM
5
1

107
107107
1071101006
IBM
6
1

180
107180
1071101007
IBM
7
1

091
107091
1071101011
VIEWSONIC
1
1

100
107100
1071101012
VIEWSONIC
2
1

104
107104
1071101013
VIEWSONIC
3
1

105
107105
1071101014
VIEWSONIC
4
1

106
107106
1071101015
VIEWSONIC
5
1

107
107107
1071101016
VIEWSONIC
6
1

180
107180
1071101017
VIEWSONIC
7
1

091
107091
1071101021
NBASUS
1
1

100
107100
1071101022
NBASUS
2
1

104
107104
1071101023
NBASUS
3
1

105
107105
1071101024
NBASUS
4
1

106
107106
1071101025
NBASUS
5
1

107
107107
1071101026
NBASUS
6
1

180
107180
1071101027
NBASUS
7
1


CREATE TABLE spec 
	( [PRD_KIND] varchar(20),[PRD_ID] varchar(17), [PRD_NT] int);
INSERT INTO spec
	([PRD_KIND],[PRD_ID], [PRD_NT])
VALUES
('PCType_A','IBM', 20000),
('PCType_B','IBM', 30000),
('PCType_C','IBM', 40000),
('MOType_L','VIEWSONIC', 25000),
('MOType_M','VIEWSONIC', 35000),
('MOType_N','VIEWSONIC', 45000),
('NBType_P','NBASUS', 50000),
('NBType_Q','NBASUS', 60000),
('NBType_R','NBASUS', 70000);
9 rows affected

select * 
from spec as b


PRD_KIND
PRD_ID
PRD_NT


PCType_A
IBM
20000

PCType_B
IBM
30000

PCType_C
IBM
40000

MOType_L
VIEWSONIC
25000

MOType_M
VIEWSONIC
35000

MOType_N
VIEWSONIC
45000

NBType_P
NBASUS
50000

NBType_Q
NBASUS
60000

NBType_R
NBASUS
70000


CREATE TABLE dept
	( [team_id] varchar(7),  [team_CName] varchar(20), [dept_id] varchar(7));
INSERT INTO dept
	([team_id], [team_CName],[dept_id])
VALUES
('AA', 'AA_Name','091'),
('B0', 'B0_Name','100'),
('BA', 'BA_Name','104'),
('BA', 'BA_Name','105'),
('BA', 'BA_Name','106'),
('BA', 'BA_Name','107'),
('BA', 'BA_Name','180');
7 rows affected

select * 
from dept as c


team_id
team_CName
dept_id


AA
AA_Name
091

B0
B0_Name
100

BA
BA_Name
104

BA
BA_Name
105

BA
BA_Name
106

BA
BA_Name
107

BA
BA_Name
180


CREATE TABLE Form
	( [BPM_serial] varchar(20),  [end_time] varchar(10));
INSERT INTO Form
	([BPM_serial], [end_time])
VALUES
('1071101001','1090101'),
('1071101002','1090101'),
('1071101003','1090101'),
('1071101004','1090101'),
('1071101005','1090101'),
('1071101006','1090101'),
('1071101007','1090101'),
('1071101011','1090101'),
('1071101012','1090101'),
('1071101013','1090101'),
('1071101014','1090101'),
('1071101015','1090101'),
('1071101016','1090101'),
('1071101017','1090101'),
('1071101021','1090101'),
('1071101022','1090101'),
('1071101023','1090101'),
('1071101024','1090101'),
('1071101025','1090101'),
('1071101026','1090101'),
('1071101027','1090101');
21 rows affected

select * 
from Form as d


BPM_serial
end_time


1071101001
1090101

1071101002
1090101

1071101003
1090101

1071101004
1090101

1071101005
1090101

1071101006
1090101

1071101007
1090101

1071101011
1090101

1071101012
1090101

1071101013
1090101

1071101014
1090101

1071101015
1090101

1071101016
1090101

1071101017
1090101

1071101021
1090101

1071101022
1090101

1071101023
1090101

1071101024
1090101

1071101025
1090101

1071101026
1090101

1071101027
1090101


-- 樓主 hheyjen 原 SQL
SELECT
d.team_id as 區處編號
,d.team_CName as 區處名稱
,SUBSTRING(a.預算邊號,4,3) as 預算部門
,isnull(c.已承租主機,0) as 已承租主機
,isnull(c.每月主機租金,0) as 每月主機租金
,(isnull(c.每月主機租金,0))*12 as 每年總租金
FROM device as a

LEFT OUTER JOIN spec as b on a.型號=b.PRD_ID

LEFT OUTER JOIN
(SELECT SUBSTRING(a.預算邊號,4,3) as 預算部門,count(b.PRD_KIND) as 已承租主機,sum(b.PRD_NT) as 每月主機租金
FROM device as a
LEFT OUTER JOIN spec as b on a.型號=b.PRD_ID
LEFT OUTER JOIN dept as c on SUBSTRING(a.預算邊號,4,3)=c.dept_id
LEFT OUTER JOIN Form as d on a.BPM單號 = d.BPM_serial
where b.PRD_KIND like '%PC%' and d.end_time is not null and a.啟用狀態='1'
group by c.team_id,c.team_CName,SUBSTRING(a.預算邊號,4,3)) as c on SUBSTRING(a.預算邊號,4,3)=c.預算部門

LEFT OUTER JOIN dept as d on SUBSTRING(a.預算邊號,4,3)=d.dept_id

group by d.team_id
,d.team_CName
,SUBSTRING(a.預算邊號,4,3)
,c.已承租主機
,c.每月主機租金


區處編號
區處名稱
預算部門
已承租主機
每月主機租金
每年總租金


AA
AA_Name
091
3
90000
1080000

B0
B0_Name
100
3
90000
1080000

BA
BA_Name
104
3
90000
1080000

BA
BA_Name
105
3
90000
1080000

BA
BA_Name
106
3
90000
1080000

BA
BA_Name
107
3
90000
1080000

BA
BA_Name
180
3
90000
1080000


-- Roger SQL
SELECT c.team_id as 區處編號,
c.team_CName as 區處名稱,
SUBSTRING(a.預算邊號,4,3) as 預算部門,
isnull(count(b.PRD_KIND),0) as 已承租主機,
sum(isnull(b.PRD_NT,0)) as 每月主機租金,
sum(isnull(b.PRD_NT,0))*12 as 每年總租金
FROM device as a
LEFT OUTER JOIN spec as b on a.型號=b.PRD_ID
LEFT OUTER JOIN dept as c on SUBSTRING(a.預算邊號,4,3)=c.dept_id
LEFT OUTER JOIN Form as d on a.BPM單號 = d.BPM_serial
where b.PRD_KIND like '%PC%' and d.end_time is not null and a.啟用狀態='1'
group by c.team_id,c.team_CName,SUBSTRING(a.預算邊號,4,3)


區處編號
區處名稱
預算部門
已承租主機
每月主機租金
每年總租金


AA
AA_Name
091
3
90000
1080000

B0
B0_Name
100
3
90000
1080000

BA
BA_Name
104
3
90000
1080000

BA
BA_Name
105
3
90000
1080000

BA
BA_Name
106
3
90000
1080000

BA
BA_Name
107
3
90000
1080000

BA
BA_Name
180
3
90000
1080000


-- 假設樓主 hheyjen 期望報表 PART I
SELECT c.team_id as 區處編號,
c.team_CName as 區處名稱,
isnull(count(b.PRD_KIND),0) as 已承租主機,
sum(isnull(b.PRD_NT,0)) as 每月主機租金,
sum(isnull(b.PRD_NT,0))*12 as 每年總租金,
sum(isnull(a.可承租數量,0)) as '主機可承租數量'
FROM device as a
LEFT OUTER JOIN spec as b on a.型號=b.PRD_ID
LEFT OUTER JOIN dept as c on SUBSTRING(a.預算邊號,4,3)=c.dept_id
LEFT OUTER JOIN Form as d on a.BPM單號 = d.BPM_serial
where b.PRD_KIND like '%PC%' and d.end_time is not null and a.啟用狀態='1'
group by c.team_id,c.team_CName
order by c.team_id


區處編號
區處名稱
已承租主機
每月主機租金
每年總租金
主機可承租數量


AA
AA_Name
3
90000
1080000
3

B0
B0_Name
3
90000
1080000
6

BA
BA_Name
15
450000
5400000
75


-- 假設樓主 hheyjen 期望報表 PART II
select distinct team_id as 區處編號,team_CName as 區處名稱,
ma.已承租主機,
ma.每月主機租金,
mb.已承租螢幕,
mb.每月螢幕租金,
mc.已承租筆電,
mc.每月筆電租金,
--
ma.主機可承租數量-ma.已承租主機 as '尚可承租主機',
mb.螢幕可承租數量-mb.已承租螢幕 as '尚可承租螢幕',
mc.筆電可承租數量-mc.已承租筆電 as '尚可承租筆電',
--
ma.每月主機租金+mb.每月螢幕租金+mc.每月筆電租金 as 每月總租金,
ma.每年主機總租金+mb.每年螢幕總租金+mc.每年筆電總租金 as 每年總租金
from dept as cx
--主機
left join (
SELECT c.team_id as 區處編號,
c.team_CName as 區處名稱,
isnull(count(b.PRD_KIND),0) as 已承租主機,
sum(isnull(b.PRD_NT,0)) as 每月主機租金,
sum(isnull(b.PRD_NT,0))*12 as 每年主機總租金,
sum(isnull(a.可承租數量,0)) as '主機可承租數量'
FROM device as a
LEFT OUTER JOIN spec as b on a.型號=b.PRD_ID
LEFT OUTER JOIN dept as c on SUBSTRING(a.預算邊號,4,3)=c.dept_id
LEFT OUTER JOIN Form as d on a.BPM單號 = d.BPM_serial
where b.PRD_KIND like '%PC%' 
and d.end_time is not null 
and a.啟用狀態='1'
group by c.team_id,c.team_CName
) as ma on ma.區處編號=cx.team_id and ma.區處名稱=cx.team_CName
--螢幕
left join (
SELECT c.team_id as 區處編號,
c.team_CName as 區處名稱,
isnull(count(b.PRD_KIND),0) as 已承租螢幕,
sum(isnull(b.PRD_NT,0)) as 每月螢幕租金,
sum(isnull(b.PRD_NT,0))*12 as 每年螢幕總租金,
sum(isnull(a.可承租數量,0)) as '螢幕可承租數量'
FROM device as a
LEFT OUTER JOIN spec as b on a.型號=b.PRD_ID
LEFT OUTER JOIN dept as c on SUBSTRING(a.預算邊號,4,3)=c.dept_id
LEFT OUTER JOIN Form as d on a.BPM單號 = d.BPM_serial
where b.PRD_KIND like '%MO%' 
and d.end_time is not null 
and a.啟用狀態='1'
group by c.team_id,c.team_CName
) as mb on mb.區處編號=cx.team_id and mb.區處名稱=cx.team_CName
--筆電
left join (
SELECT c.team_id as 區處編號,
c.team_CName as 區處名稱,
isnull(count(b.PRD_KIND),0) as 已承租筆電,
sum(isnull(b.PRD_NT,0)) as 每月筆電租金,
sum(isnull(b.PRD_NT,0))*12 as 每年筆電總租金,
sum(isnull(a.可承租數量,0)) as '筆電可承租數量'
FROM device as a
LEFT OUTER JOIN spec as b on a.型號=b.PRD_ID
LEFT OUTER JOIN dept as c on SUBSTRING(a.預算邊號,4,3)=c.dept_id
LEFT OUTER JOIN Form as d on a.BPM單號 = d.BPM_serial
where b.PRD_KIND like '%NB%' 
and d.end_time is not null 
and a.啟用狀態='1'
group by c.team_id,c.team_CName
) as mc on mc.區處編號=cx.team_id and mc.區處名稱=cx.team_CName
--
order by cx.team_id


區處編號
區處名稱
已承租主機
每月主機租金
已承租螢幕
每月螢幕租金
已承租筆電
每月筆電租金
尚可承租主機
尚可承租螢幕
尚可承租筆電
每月總租金
每年總租金


AA
AA_Name
3
90000
3
105000
3
180000
0
0
0
375000
4500000

B0
B0_Name
3
90000
3
105000
3
180000
3
3
3
375000
4500000

BA
BA_Name
15
450000
15
525000
15
900000
60
60
60
1875000
22500000

預算部門	預算邊號	BPM單號	型號	可承租數量	啟用狀態
091	107091	1071101001	IBM	1	1
100	107100	1071101002	IBM	2	1
104	107104	1071101003	IBM	3	1
105	107105	1071101004	IBM	4	1
106	107106	1071101005	IBM	5	1
107	107107	1071101006	IBM	6	1
180	107180	1071101007	IBM	7	1
091	107091	1071101011	VIEWSONIC	1	1
100	107100	1071101012	VIEWSONIC	2	1
104	107104	1071101013	VIEWSONIC	3	1
105	107105	1071101014	VIEWSONIC	4	1
106	107106	1071101015	VIEWSONIC	5	1
107	107107	1071101016	VIEWSONIC	6	1
180	107180	1071101017	VIEWSONIC	7	1
091	107091	1071101021	NBASUS	1	1
100	107100	1071101022	NBASUS	2	1
104	107104	1071101023	NBASUS	3	1
105	107105	1071101024	NBASUS	4	1
106	107106	1071101025	NBASUS	5	1
107	107107	1071101026	NBASUS	6	1
180	107180	1071101027	NBASUS	7	1

PRD_KIND	PRD_ID	PRD_NT
PCType_A	IBM	20000
PCType_B	IBM	30000
PCType_C	IBM	40000
MOType_L	VIEWSONIC	25000
MOType_M	VIEWSONIC	35000
MOType_N	VIEWSONIC	45000
NBType_P	NBASUS	50000
NBType_Q	NBASUS	60000
NBType_R	NBASUS	70000

team_id	team_CName	dept_id
AA	AA_Name	091
B0	B0_Name	100
BA	BA_Name	104
BA	BA_Name	105
BA	BA_Name	106
BA	BA_Name	107
BA	BA_Name	180

BPM_serial	end_time
1071101001	1090101
1071101002	1090101
1071101003	1090101
1071101004	1090101
1071101005	1090101
1071101006	1090101
1071101007	1090101
1071101011	1090101
1071101012	1090101
1071101013	1090101
1071101014	1090101
1071101015	1090101
1071101016	1090101
1071101017	1090101
1071101021	1090101
1071101022	1090101
1071101023	1090101
1071101024	1090101
1071101025	1090101
1071101026	1090101
1071101027	1090101

區處編號	區處名稱	預算部門	已承租主機	每月主機租金	每年總租金
AA	AA_Name	091	3	90000	1080000
B0	B0_Name	100	3	90000	1080000
BA	BA_Name	104	3	90000	1080000
BA	BA_Name	105	3	90000	1080000
BA	BA_Name	106	3	90000	1080000
BA	BA_Name	107	3	90000	1080000
BA	BA_Name	180	3	90000	1080000

區處編號	區處名稱	已承租主機	每月主機租金	已承租螢幕	每月螢幕租金	已承租筆電	每月筆電租金	尚可承租主機	尚可承租螢幕	尚可承租筆電	每月總租金	每年總租金
AA	AA_Name	3	90000	3	105000	3	180000	0	0	0	375000	4500000
B0	B0_Name	3	90000	3	105000	3	180000	3	3	3	375000	4500000
BA	BA_Name	15	450000	15	525000	15	900000	60	60	60	1875000	22500000