Understanding Query

%3CLINGO-SUB%20id%3D%22lingo-sub-2367450%22%20slang%3D%22en-US%22%3EUnderstanding%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2367450%22%20slang%3D%22en-US%22%3E%3CP%3E%3BWITH%20QuoteTotals%20AS%3CBR%20%2F%3E(%3CBR%20%2F%3Eselect%20QuoteId%3CBR%20%2F%3E%2CQuoteTotal%3CBR%20%2F%3E%2CROW_NUMBER()%20OVER(PARTITION%20BY%20QuoteId%20ORDER%20BY%20RecordId%20DESC)%20AS%20RowNum%3CBR%20%2F%3E%2CInsertedOn%3CBR%20%2F%3E%2Crecordid%3CBR%20%2F%3E%2Cofflinetotal%3CBR%20%2F%3E%2Conlinetotal%3CBR%20%2F%3E%2Cusercreatedtotal%3CBR%20%2F%3E%2CO.QuoteId%3CBR%20%2F%3E%2CT.ProcessedOn%3CBR%20%2F%3Efrom%20QuoteTotals%20T%20WITH(NOLOCK)%3CBR%20%2F%3Einner%20join%20Dynamics.dbo.Quote%20O%20ON%20(T.QuoteId%20%3D%20O.QuoteId)%3CBR%20%2F%3EWHERE%20o.Period%20IN%20('2012'%2C'2013')%3CBR%20%2F%3E)%3CBR%20%2F%3E--UPDATE%20QuoteTotals%3CBR%20%2F%3E--SET%20status%20%3D%20'ACTIVE'%2C%20statusreason%20%3D%20'NEW'%3CBR%20%2F%3E--WHERE%20recordid%20IN%3CBR%20%2F%3E--(%3CBR%20%2F%3Eselect%20MIN(ot.RowNum)%2Cot.QuoteId%2C%20MIN(ot.QuoteTotal)%20'IntegrationCalculatedTotal'%2C%20MIN(o.EstimatedValue)%20'CurrentQuoteTotal'%2C%20MIN(ot.OnlineTotal)%20'IntegrationCalculatedOnlineAmount'%2C%20MIN(o.sc_ym_onlineamount)%20'CurrentQuoteOnlineAmount'%2CMIN(ot.OfflineTotal)%20'IntegrationCalculatedOfflineAmount'%2C%20MIN(O.EstimatedValue%20-%20o.sc_ym_onlineamount)%20AS%20'CurrentQuoteOfflineAmount'%2C%20MIN(ot.RecordId)%2Cot.QuoteId%3CBR%20%2F%3Efrom%20QuoteTotals%20ot%3CBR%20%2F%3EINNER%20JOIN%20Dynamics.dbo.Quote%20o%20ON%20(o.QuoteId%20%3D%20ot.QuoteId)%3CBR%20%2F%3E--WHERE%20ot.RowNum%20%3D%202%20AND%20ot.QuoteTotal%20%26gt%3B%20CAST(o.EstimatedValue%20AS%20DECIMAL(18%2C2))%20AND%20ot.ProcessedOn%20%26lt%3B%20'2021-05-01'%3CBR%20%2F%3EWHERE%20o.ActualValue%20%3D%20ot.QuoteTotal%20AND%20o.ActualValue%20%26lt%3B%26gt%3B%20o.EstimatedValue%3CBR%20%2F%3EGROUP%20BY%20ot.QuoteId%2C%20ot.QuoteId%3CBR%20%2F%3E--)%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

;WITH QuoteTotals AS
(
select QuoteId
,QuoteTotal
,ROW_NUMBER() OVER(PARTITION BY QuoteId ORDER BY RecordId DESC) AS RowNum
,InsertedOn
,recordid
,offlinetotal
,onlinetotal
,usercreatedtotal
,O.QuoteId
,T.ProcessedOn
from QuoteTotals T WITH(NOLOCK)
inner join Dynamics.dbo.Quote O ON (T.QuoteId = O.QuoteId)
WHERE o.Period IN ('2012','2013')
)
--UPDATE QuoteTotals
--SET status = 'ACTIVE', statusreason = 'NEW'
--WHERE recordid IN
--(
select MIN(ot.RowNum),ot.QuoteId, MIN(ot.QuoteTotal) 'IntegrationCalculatedTotal', MIN(o.EstimatedValue) 'CurrentQuoteTotal', MIN(ot.OnlineTotal) 'IntegrationCalculatedOnlineAmount', MIN(o.sc_ym_onlineamount) 'CurrentQuoteOnlineAmount',MIN(ot.OfflineTotal) 'IntegrationCalculatedOfflineAmount', MIN(O.EstimatedValue - o.sc_ym_onlineamount) AS 'CurrentQuoteOfflineAmount', MIN(ot.RecordId),ot.QuoteId
from QuoteTotals ot
INNER JOIN Dynamics.dbo.Quote o ON (o.QuoteId = ot.QuoteId)
--WHERE ot.RowNum = 2 AND ot.QuoteTotal > CAST(o.EstimatedValue AS DECIMAL(18,2)) AND ot.ProcessedOn < '2021-05-01'
WHERE o.ActualValue = ot.QuoteTotal AND o.ActualValue <> o.EstimatedValue
GROUP BY ot.QuoteId, ot.QuoteId
--)

0 Replies