Skip to Content
0
Former Member
Jul 04, 2011 at 02:54 PM

SQL Query - merging figures from multiple table

15 Views

Hi Experts

I am trying to create a Report of all AR Invoices and AR Credit Notes generated in a date range.

This is the quuery I am using:

SELECT T0.CardCode, T0.CardName, T1.GroupCode, T1.U_Sub_Grp, T1.SlpCode, SUM(T0.DocTotalSy) as "Gross Turnover", SUM(T0.VatSumSy) as "VAT", SUM(T0.DocTotalSy-T0.VatSumSy) as "Net Turnover"

FROM OINV T0 INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode

WHERE T0.TaxDate >='[%1]' and T0.Taxdate <= '[%2]'

GROUP BY T0.CardCode, T0.CardName, T1.GroupCode, T1.SlpCode, T1.U_Sub_Grp

UNION

SELECT T0.CardCode, T0.CardName, T1.GroupCode, T1.U_Sub_Grp, T1.SlpCode, SUM (T0.DocTotalSy)-1, SUM(T0.VatSumSy)-1, SUM(T0.DocTotalSy-T0.VatSumSy)*-1 as "Net Inv"

FROM ORIN T0 INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode

WHERE T0.TaxDate >='[%1]' and t0.Taxdate <= '[%2]'

GROUP BY T0.CardCode, T0.CardName, T1.GroupCode, T1.SlpCode, T1.U_Sub_Grp

However, when you run it, it displays duplicate customer details first total from the AR Invoice for each customer and secondly from the AR credit. Any chances I can get the two figures truly merged and therefore have just one unique customer code line without duplication.

I would appreciate any help.

Tony