/*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*/
Diposkan oleh
