Skip to end of metadata
Go to start of metadata
DECLARE @pst TABLE (AccLink bigint INDEX IX2c NONCLUSTERED, Period int INDEX IX2e NONCLUSTERED,Reference varchar(50) INDEX IX2 NONCLUSTERED, DmC float
						, INDEX IX3 NONCLUSTERED(AccLink,Period,Reference))
DECLARE @pgl TABLE (AccLink bigint INDEX IX2cc NONCLUSTERED, Period int INDEX IX2f NONCLUSTERED,Reference varchar(50) INDEX IX2b NONCLUSTERED, DmC float
						, INDEX IX3b NONCLUSTERED(AccLink,Period,Reference))


INSERT INTO @pst
select G.SalesAccLink,PP.idPeriod,P.Reference, SUM(P.Debit-P.Credit) as DmC from PostST P 
		LEFT JOIN StkItem S ON P.AccountLink = S.StockLink
		LEFT JOIN _etblStockDetails SD ON S.StockLink = SD.StockID
		LEFT JOIN GrpTbl G ON SD.GroupID = G.idGrpTbl
		LEFT JOIN (SELECT DATEADD(day,1,P2.dPeriodDate) as FromDate, P.* FROM _etblPeriod P LEFT JOIN _etblPeriod P2 ON P.idPeriod-1 = P2.idPeriod) PP ON P.TxDate BETWEEN PP.FromDate and PP.dPeriodDate
		where Id IN ('oInv','Crn') AND TxDate >= '2021-03-01'
		group by G.SalesAccLink,PP.idPeriod,P.Reference


INSERT INTO @pgl
select P.AccountLink,P.Period,P.Reference, SUM(P.Debit-P.Credit) as DmC from PostGL P 
left join Accounts A ON P.AccountLink = A.AccountLink
			LEFT JOIN _etblGLAccountTypes AT ON A.iAccountType = AT.idGLAccountType
			WHERE AT.cAccountTypeDescription like 'Reve%'
				and Id IN ('oInv','Crn') AND TxDate >= '2021-03-01'
		group by P.AccountLink,P.Period,P.Reference

select FullRef, SUM(Dmc) FROM (
SELECT CONCAT(AccLink,Period,Reference) as FullRef, AccLink,Period,Reference,-Dmc as Dmc FROM @pst --order by Reference
union all
SELECT CONCAT(AccLink,Period,Reference) as FullRef, AccLink,Period,Reference,Dmc FROM @pgl --order by Reference
) ss group by ss.FullRef having ABS(SUM(Dmc)) > 0.01
  • No labels