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), [啟用狀態] varchar(2));
INSERT INTO device
	([預算邊號], [BPM單號],[型號],[啟用狀態])
VALUES
('107091', '1071101001','IBM', '1'),
('107100', '1071101002','IBM', '1'),
('107104', '1071101003','IBM', '1'),
('107105', '1071101004','IBM', '1'),
('107106', '1071101005','IBM', '1'),
('107107', '1071101006','IBM', '1'),
('107180', '1071101007','IBM', '1');
7 rows affected

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


預算部門
預算邊號
BPM單號
型號
啟用狀態


091
107091
1071101001
IBM
1

100
107100
1071101002
IBM
1

104
107104
1071101003
IBM
1

105
107105
1071101004
IBM
1

106
107106
1071101005
IBM
1

107
107107
1071101006
IBM
1

180
107180
1071101007
IBM
1


CREATE TABLE spec 
	( [PRD_KIND] varchar(20),[PRD_ID] varchar(7), [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);
3 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


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


-- 樓主 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 期望報表
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 每年總租金
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

B0
B0_Name
3
90000
1080000

BA
BA_Name
15
450000
5400000

預算部門	預算邊號	BPM單號	型號	啟用狀態
091	107091	1071101001	IBM	1
100	107100	1071101002	IBM	1
104	107104	1071101003	IBM	1
105	107105	1071101004	IBM	1
106	107106	1071101005	IBM	1
107	107107	1071101006	IBM	1
180	107180	1071101007	IBM	1

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

區處編號	區處名稱	預算部門	已承租主機	每月主機租金	每年總租金
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