Query for checking

/*before running this script run the report from backoffice first to store the data to table itemsales*/
/*this query will find out variance between sales after tax/gross sales and collected*/


--1.run this script to group all item per salesno
SELECT salesno,splitno,
Sum(([ItemSales].[Quantity]*[ItemSales].[ItemAmount])*case when
([ItemSales].[TransMode]='REG') then 1 else -1 end *case when ((FunctionID)=26)
then 1 else 0 end ) AS ItemSales,
Sum(((Discount)+(PromotionSaving))*case when
((FunctionID)=26) then 1 else 0 end *case when ((DiscountType)<>'FOC Item') then
1 else 0 end *case when ((TransMode)='REG') then 1 else -1 end ) AS ItemDisc,
Sum((ItemAmount)*case when ((FunctionID)=25) then 1 else 0 end *case when
((TransMode)='REG') then 1 else -1 end ) AS BillDisc,
(
Sum(([ItemAmount]*[Quantity])*case when (([FOCType])='FOC Item') then 1 else 0
end *case when ((FunctionID)=26) then 1 else 0 end *case when
(([TransMode])='REG') then 1 else -1 end )) AS TFOC,
Sum(([ItemAmount])*case
when ((FunctionID)=7) then 1 else 0 end *case when (([TransMode])='REG') then 1
else -1 end ) AS BFOC, Sum((Tax0)*case when ((TransMode)='REG') then 1 else -1
end ) AS TTax0,
Sum((Tax1)*case when ((TransMode)='REG') then 1 else -1 end
*case when FunctionID=1  or FunctionID=2  or FunctionID=3  or FunctionID=4  or
FunctionID=5  or FunctionID=6  or FunctionID=8  or FunctionID=9  then 1 else 0
end ) AS TTax1,
Sum((Gratuity)*case when ((TransMode)='REG') then 1 else -1 end
) AS TGratuity,
Sum((RndingAdjustments)*case when ((TransMode)='REG') then 1
else -1 end ) AS TRndAdj,
Sum(([ItemAmount]+[Gratuity])*case when FunctionID=1
or FunctionID=2  or FunctionID=3  or FunctionID=4  or FunctionID=5  or
FunctionID=6  or FunctionID=8  or FunctionID=9  then 1 else 0 end *case when
(([TransMode])='REG') then 1 else -1 end ) AS Collected,
Sum(([ItemSales].[Covers])*case when ([ItemSales].[TransMode]='REG') then 1 else
-1 end *case when ([ItemSales].[TransStatus]='S') then 1 else 0 end ) AS
TCovers, Sum(([ItemSales].[Quantity])*case when ([ItemSales].[TransMode]='REG')
then 1 else -1 end *case when ([ItemSales].[TransStatus]='S') then 1 else 0 end
) AS TBills
, Sum(RndingAdjustments) as RndingAdjustments
into #tot
FROM ItemSales WHERE ((ItemSales.TransStatus)=' ') OR
((ItemSales.TransStatus)='S') or ((Itemsales.TransStatus)='E')
group by salesno,splitno


--2.run this script to get netsales and tax
select salesno,itemsales,itemsales-itemdisc-billdisc-tfoc-bfoc as netsales,collected-RndingAdjustments as collected,tgratuity,ttax0,ttax1
into #tot2
from #tot

--3.a run this script for exclusive tax
select salesno,itemsales,netsales+ttax0+ttax1+tgratuity as NetAndTax,collected
into #tot3
from #tot2

--3.b run this script for inclusive tax
select salesno,itemsales,netsales+tgratuity as NetAndTip,collected
into #tot3
from #tot2

--4.run this script to find out the variance
select * from #tot3
where netandtax<>collected


/*after you find out wrong salesno,go to salesitemstemp and anlyze the data to find out the wrong record*/