-
Notifications
You must be signed in to change notification settings - Fork 0
/
Pivot_Example.sql
65 lines (59 loc) · 2.29 KB
/
Pivot_Example.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
DECLARE @rptBeginDt DateTime = DateAdd(yy, -2, GetDate());
SELECT [Customer_ID],
[PRI2PTO],
[PTO2PFI],
[PRI2HRI],
[HRI2HFI],
[PRI2ERI],
[ERI2ETO]
FROM (
SELECT B.Customer_ID
, DATEDIFF(dd, A.Click_Bill_Date, C.Click_Bill_Date) deltaDD
, CASE WHEN A.Service_Type = 'PLUMBING' AND A.Phase_Code = 'RI'
AND C.Service_Type = 'PLUMBING' AND C.Phase_Code = 'TO' THEN 'PRI2PTO'
WHEN A.Service_Type = 'PLUMBING' AND A.Phase_Code = 'TO'
AND C.Service_Type = 'PLUMBING' AND C.Phase_Code = 'FIN' THEN 'PTO2PFI'
WHEN A.Service_Type = 'PLUMBING' AND A.Phase_Code = 'RI'
AND C.Service_Type = 'HVAC' AND C.Phase_Code = 'RI' THEN 'PRI2HRI'
WHEN A.Service_Type = 'HVAC' AND A.Phase_Code = 'RI'
AND C.Service_Type = 'HVAC' AND C.Phase_Code = 'FIN' THEN 'HRI2HFI'
WHEN A.Service_Type = 'PLUMBING' AND A.Phase_Code = 'RI'
AND C.Service_Type = 'ELECTRICAL' AND C.Phase_Code = 'RI' THEN 'PRI2ERI'
WHEN A.Service_Type = 'ELECTRICAL' AND A.Phase_Code = 'RI'
AND C.Service_Type = 'ELECTRICAL' AND C.Phase_Code = 'TO' THEN 'ERI2ETO'
END catNames
FROM BI.JC_S_P_Stats A JOIN
dbo.Job_Information B ON A.Job_Code = B.Job_Code JOIN
BI.JC_S_P_Stats C ON A.Job_Code = C.Job_Code JOIN
Subdivisions D ON B.Subdivision_ID = D.Subdivision_ID JOIN
Divisions E ON D.Division_ID = E.Division_ID
WHERE ( B.Crawl_Space <> 'C' OR B.Crawl_Space IS NULL )
AND A.Click_Bill_Date > @rptBeginDt
AND E.Company_Division_ID = 2
AND A.Click_Bill_Date IS NOT NULL AND C.Click_Bill_Date IS NOT NULL
AND A.Click_Bill_Date < C.Click_Bill_Date
AND ( (A.Service_Type = 'PLUMBING' AND A.Phase_Code = 'RI'
AND C.Service_Type = 'PLUMBING' AND C.Phase_Code = 'TO')
OR (A.Service_Type = 'PLUMBING' AND A.Phase_Code = 'TO'
AND C.Service_Type = 'PLUMBING' AND C.Phase_Code = 'FIN')
OR (A.Service_Type = 'PLUMBING' AND A.Phase_Code = 'RI'
AND C.Service_Type = 'HVAC' AND C.Phase_Code = 'RI')
OR (A.Service_Type = 'HVAC' AND A.Phase_Code = 'RI'
AND C.Service_Type = 'HVAC' AND C.Phase_Code = 'FIN')
OR (A.Service_Type = 'PLUMBING' AND A.Phase_Code = 'RI'
AND C.Service_Type = 'ELECTRICAL' AND C.Phase_Code = 'RI')
OR (A.Service_Type = 'ELECTRICAL' AND A.Phase_Code = 'RI'
AND C.Service_Type = 'ELECTRICAL' AND C.Phase_Code = 'TO')
)
) Q
PIVOT ( AVG(deltaDD)
FOR catNames IN (
[PRI2PTO],
[PTO2PFI],
[PRI2HRI],
[HRI2HFI],
[PRI2ERI],
[ERI2ETO]
)
) AS pivot_table
ORDER BY 1