/*
-- To manually run aggregation
EXEC [qryMMS_Financial_OffsiteSalesDailyAggregate]

-- To clear the Aggregation
TRUNCATE TABLE [tbOffsiteSalesDailyAggregate]
UPDATE Forecasting.TableHighWatermark SET DailyHighWatermark = 0 WHERE TableName = 'tbTransactionSummary.OffsiteSales'

-- For testing Purge process, here is some dummy data for > 4 years in the past
INSERT INTO tbOffsiteSalesDailyAggregate (EntityId, BusinessDay, OffsiteSaleLocationId, Sales)
VALUES (123, '2/21/2015', 123, 50.0)
INSERT INTO tbOffsiteSalesDailyAggregate (EntityId, BusinessDay, OffsiteSaleLocationId, Sales)
VALUES (123, '1/21/2015', 123, 50.0)
INSERT INTO tbOffsiteSalesDailyAggregate (EntityId, BusinessDay, OffsiteSaleLocationId, Sales)
VALUES (123, '12/21/2014', 123, 50.0)
*/


; WITH Actuals (EntityID, BusinessDay, OffsiteSaleLocationId, Sales)
AS (
	SELECT ts.EntityID, ts.BusinessDay, osl.OffsiteSaleLocationId, CAST(SUM(ts.Sales_Incl_GST_EXCL) AS money)
	FROM tbTransactionSummary ts WITH(NOLOCK)
	JOIN tbTransactionOffsiteSalesLocation osl WITH(NOLOCK) ON osl.TransactionSummaryId = ts.TransactionSummaryID
	GROUP BY ts.EntityID, ts.BusinessDay, osl.OffsiteSaleLocationId
),
Stored (EntityID, BusinessDay, OffsiteSaleLocationId, Sales)
AS (
	SELECT EntityID, BusinessDay, OffsiteSaleLocationId, CAST(SUM(Sales) AS money)
	FROM tbOffsiteSalesDailyAggregate WITH(NOLOCK)
	GROUP BY EntityID, BusinessDay, OffsiteSaleLocationId
)
SELECT COALESCE(a.EntityID, s.EntityID) EntityID, 
	COALESCE(a.BusinessDay, s.BusinessDay) BusinessDay, 
	COALESCE(a.OffsiteSaleLocationId, s.OffsiteSaleLocationId) OffsiteSaleLocationId, 
	a.Sales ActualSales, 
	s.Sales StoredSales 
FROM Actuals a
FULL JOIN Stored s ON a.EntityID = s.EntityID AND a.BusinessDay = s.BusinessDay
	AND a.OffsiteSaleLocationId = s.OffsiteSaleLocationId
WHERE ISNULL(a.Sales,0) <> ISNULL(s.Sales,0)
