************************************Internal to Citixsys****************************************
Problem Statement
SQL Query to verify results of default iVend report "Sales Transaction by Store report (Refund included) "
Solution
Below query can be used to get a transaction level detail of the records /result coming from the default iVend report ( Sale transaction by store).
Date filter need to be given as per requirement
Select S.Id, Sum(CASE WHEN T1.Type=1 THEN -1*T1.TotalPostSaleDiscount ELSE T1.TotalPostSaleDiscount END ) OVER(Order BY S.Id), Sum(CASE WHEN T1.Type=1 THEN -1*T1.TotalPostSaleDiscount ELSE T1.TotalPostSaleDiscount END ) OVER() From TrxTransaction T WITH (NOLOCK) INNER JOIN
TrxTransactionSaleItem T1 WITH (NOLOCK) ON T.TransactionKey=T1.TransactionKey
INNER JOIN RtlStore S WITH (NOLOCK) ON T.StoreKey=S.StoreKey
Where T.BusinessDate BETWEEN '2021-08-01' AND '2021-08-31'
AND T.IsVoided=0 AND T.IsSuspended=0 AND Type IN (0, 1)
Order BY 2