-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathGnarly_Procedure_To_Query_Disparate_Data.txt
247 lines (247 loc) · 7.83 KB
/
Gnarly_Procedure_To_Query_Disparate_Data.txt
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
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
CREATE PROCEDURE GetDQMData @DQMID VARCHAR(55)
AS
SELECT DISTINCT cust.CustomerName
,dqm.ConfigQuoteID AS 'ConfigQuoteID'
,dqm.ConfigQuoteName
,pg.Description AS 'PriceGroup'
,dqm.SalesRepID
,dqm.TermInMonths
,dqm.LeaseIncludeBuyout
,dqm.LeaseWrapBuyout
,dqm.LeaseBuyoutAmount
,dqm.LeaseFactor
,dqm.TotalSuggestedPrice
,CASE
WHEN mps.grandtotalcost IS NULL
THEN '0.00'
ELSE mps.grandtotalcost
END AS 'MPSServicePT'
,a.PrefFullName AS 'SalesRep'
,a.Email
,dqm.leasePayment AS 'DQMEquipPayment'
,dqm.TotalService AS 'DQMTotalService'
,dqm.TotalOutcostCalculated
,sum(sd.Volume) AS 'TotalVolume'
,sum(CASE
WHEN dqmd.item LIKE '%solutions%'
THEN 1
ELSE 0
END) AS 'SolutionsItemCount'
,sum(CASE
WHEN dqmd.item NOT LIKE '%solutions%'
THEN 1
ELSE 0
END) AS 'NonSolutionsItemCount'
,dqm.ServiceBaseCost AS 'DQMServiceBaseCost'
,sum(CASE
WHEN configs.Description LIKE '%unlimited%'
THEN 1
ELSE 0
END) AS 'UnlimitedItemCount'
,sum(CASE
WHEN configs.Description NOT LIKE '%unlimited%'
THEN 1
ELSE 0
END) AS 'NotUnlimitedItemCount'
,cms.DQMID AS 'SessionDQMID'
,cms.TotalCustomerPayment AS 'SessionTotalCustomerPayment'
,cms.MPSServicePayment AS 'SessionMPSServicePayment'
,cms.CopierServicePayment AS 'SessionCopierServicePayment'
,cms.LeaseFactor AS 'SessionLeaseFactor'
,cms.BlackOverageSalePrice AS 'SessionBlackOverageSalePrice'
,cms.ColorOverageSalePrice AS 'SessionColorOverageSalePrice'
,cms.OutrightChecked AS 'SessionOutrightChecked'
,cms.TradeChecked AS 'SessionTradeChecked'
,cms.NetNewChecked AS 'SessionNetNewChecked'
,cms.NetNewForLifeChecked AS 'SessionNetNewForLifeChecked'
,cms.MPSMITChecked AS 'SessionMPSMITChecked'
,cms.BidSupportChecked AS 'SessionBidSupportChecked'
,cms.K12Checked AS 'SessionK12Checked'
,cms.SubmittedToOrders AS 'SessionSubmittedToOrders'
,cms.Commercial3GChecked AS 'SessionCommercial3GChecked'
,cms.Commercial4GChecked AS 'SessionCommercial4GChecked'
,cms.SalesRepName AS 'SessionSalesRepName'
,cms.AgentID AS 'SessionAgentID'
,cms.SalePrice AS 'SessionSalePrice'
,cms.MiscRevenueAdjustment AS 'SessionMiscRevenueAdjustment'
,cms.OrderProcessorApprovalStatus AS 'SessionOrderProcessorApprovalStatus'
,cms.LeaseTerm AS 'SessionLeaseTerm'
,cms.NoServiceEscalation AS 'SessionNoServiceEscalation'
,cms.MatrixLeaseBuyout AS 'SessionMatrixLeaseBuyout'
,cms.ExistingContractNumber AS 'SessionExistingContractNumber'
,cms.ExistingContractCustomerName AS 'SessionExistingContractCustomerName'
,ss.BlackVolumeSum
,ss.ColorVolumeSum
,ss.BlackServiceRateSum
,ss.ColorServiceRateSum
,ss.BlackOverageRateSum
,ss.ColorOverageRateSum
,ss.BlackVolumexOverageRate
,ss.ColorVolumexOverageRate
,ss.BlackServiceBaseCostSum
,ss.ColorServiceBaseCostSum
,ss.BlackTargetCustomerVolumeSum
,ss.ColorTargetCustomerVolumeSum
,ss.MPSBlackBlendedRate
,ss.MPSColorBlendedRate
,ss.MPSBlackVolume
,ss.MPSColorVolume
,ss.BlackCount
,ss.ColorCount
,ai.AddedTransferPriceSum
,ai.AddedPriceSum
,ai.AddedVolume
,ai.AddedBaseCost
,ai.AddedBlackRate
,ai.AddedColorRate
,ai.AddedBlackCount
,ai.AddedColorCount
,ai.AddedBlackRatexVolume
,ai.AddedColorRatexVolume
,ai.AddedBlackVolume
,ai.AddedColorVolume
,promo.PromoCashPayout
,promo.PromoTransferReduction
,Deductions.DeductionsAmount
,CASE
WHEN paid.DQMID IS NOT NULL
THEN 'Paid'
ELSE 'NotPaid'
END AS CommPaidStatus
FROM coMRC.dbo.saConfigQuotes dqm
LEFT JOIN DIGITAL_HUB.dbo.DD_Mpssessions mps ON dqm.ConfigQuoteID = mps.dqmnumber
LEFT JOIN saConfigQuoteDetails dqmd ON dqm.configQuoteID = dqmd.configQuoteID
LEFT JOIN icConfigs configs ON dqmd.ConfigID = configs.ConfigID
LEFT JOIN (
SELECT DISTINCT dqmid
FROM DIGITAL_HUB.dbo.dahlke_CompMatrixDeals cmd
RIGHT JOIN digital_hub.dbo.CommissionsProperties cp ON cmd.id = cp.ID
) paid ON CONVERT(VARCHAR(255), dqm.ConfigQuoteID) = paid.DQMID
LEFT JOIN SAConfigServiceDetails sd ON dqmd.ConfigQuoteDetailID = sd.ConfigQuoteDetailID
LEFT JOIN shAgents a ON dqm.SalesRepID = a.AgentID
LEFT JOIN arCustomers cust ON dqm.CustomerID = cust.CustomerID
LEFT JOIN SAPriceGroups pg ON dqm.PriceGroupID = pg.PriceGroupID
LEFT JOIN digital_hub.dbo.CompMatrixSessions cms ON dqm.configQuoteID = cms.DQMID
LEFT JOIN DQMDataServiceSummary ss ON dqm.configquoteID = ss.configQuoteID
LEFT JOIN (
SELECT i.DQMID
,sum(i.AppliedCashPayout) AS 'PromoCashPayout'
,sum(i.AppliedTransferReduction) AS 'PromoTransferReduction'
FROM digital_hub.dbo.CompMatrixPromoItems i
WHERE DQMID = @DQMID
AND ItemEnabled = 1
GROUP BY i.DQMID
) promo ON dqm.ConfigQuoteID = promo.DQMID
LEFT JOIN (
SELECT dqmid
,sum(convert(DECIMAL(18, 2), TransferPrice)) AS 'AddedTransferPriceSum'
,sum(convert(DECIMAL(18, 2), HotListPrice)) AS 'AddedPriceSum'
,sum(convert(DECIMAL(18, 2), Volume)) AS 'AddedVolume'
,sum(convert(DECIMAL(18, 2), BlackVolume)) AS 'AddedBlackVolume'
,sum(convert(DECIMAL(18, 2), ColorVolume)) AS 'AddedColorVolume'
,sum(convert(DECIMAL(18, 2), BaseCost)) AS 'AddedBaseCost'
,sum(convert(DECIMAL(18, 4), BlackRate)) AS 'AddedBlackRate'
,sum(convert(DECIMAL(18, 4), BlackRate) * convert(DECIMAL(18, 2), BlackVolume)) AS 'AddedBlackRatexVolume'
,sum(convert(DECIMAL(18, 4), ColorRate) * convert(DECIMAL(18, 2), ColorVolume)) AS 'AddedColorRatexVolume'
,sum(convert(DECIMAL(18, 4), ColorRate)) AS 'AddedColorRate'
,sum(CASE
WHEN convert(DECIMAL(18, 4), BlackRate) = 0
THEN 0
ELSE 1
END) AS 'AddedBlackCount'
,sum(CASE
WHEN convert(DECIMAL(18, 4), ColorRate) = 0
THEN 0
ELSE 1
END) AS 'AddedColorCount'
FROM digital_hub.dbo.CompMatrixSessionsAddedItems
WHERE dqmid = @DQMID
GROUP BY dqmid
) ai ON dqm.ConfigQuoteID = ai.DQMID
LEFT JOIN (
SELECT DQMID
,sum(Amount) AS 'DeductionsAmount'
FROM DIGITAL_HUB.dbo.CompMatrixDeductions
WHERE DQMID = @DQMID
GROUP BY DQMID
) Deductions ON dqm.ConfigQuoteID = Deductions.DQMID
WHERE dqm.ConfigQuoteID = @DQMID
GROUP BY cust.CustomerName
,dqm.ConfigQuoteID
,dqm.ConfigQuoteName
,dqm.LeaseIncludeBuyout
,dqm.LeaseWrapBuyout
,pg.Description
,dqm.SalesRepID
,dqm.termInMonths
,dqm.LeaseBuyoutAmount
,dqm.LeaseFactor
,dqm.TotalSuggestedPrice
,a.PrefFullName
,a.Email
,dqm.LeasePayment
,dqm.TotalService
,dqm.TotalOutcostCalculated
,mps.grandtotalcost
,dqm.ServiceBaseCost
,cms.TotalCustomerPayment
,cms.MPSServicePayment
,cms.CopierServicePayment
,cms.LeaseFactor
,cms.BlackOverageSalePrice
,cms.ColorOverageSalePrice
,cms.OutrightChecked
,cms.TradeChecked
,cms.NetNewChecked
,cms.NetNewForLifeChecked
,cms.MPSMITChecked
,cms.BidSupportChecked
,cms.K12Checked
,cms.SubmittedToOrders
,cms.Commercial3GChecked
,cms.Commercial4GChecked
,cms.DQMID
,cms.SalesRepName
,cms.AgentID
,cms.SalePrice
,cms.MiscRevenueAdjustment
,cms.OrderProcessorApprovalStatus
,cms.LeaseTerm
,cms.NoServiceEscalation
,cms.MatrixLeaseBuyout
,cms.ExistingContractNumber
,cms.ExistingContractCustomerName
,ss.BlackVolumeSum
,ss.ColorVolumeSum
,ss.BlackServiceRateSum
,ss.ColorServiceRateSum
,ss.BlackOverageRateSum
,ss.ColorOverageRateSum
,ss.BlackVolumexOverageRate
,ss.ColorVolumexOverageRate
,ss.BlackServiceBaseCostSum
,ss.ColorServiceBaseCostSum
,ss.BlackTargetCustomerVolumeSum
,ss.ColorTargetCustomerVolumeSum
,ss.MPSBlackBlendedRate
,ss.MPSColorBlendedRate
,ss.MPSBlackVolume
,ss.MPSColorVolume
,ss.BlackCount
,ss.ColorCount
,ai.AddedTransferPriceSum
,ai.AddedPriceSum
,ai.AddedVolume
,ai.AddedBaseCost
,ai.AddedBlackRate
,ai.AddedColorRate
,ai.AddedBlackCount
,ai.AddedColorCount
,ai.AddedBlackRatexVolume
,ai.AddedColorRatexVolume
,ai.AddedBlackVolume
,ai.AddedColorVolume
,promo.PromoCashPayout
,promo.PromoTransferReduction
,Deductions.DeductionsAmount
,paid.DQMID