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