Hi guys
I need some assistance with a sales report, I need to write a report in crystal reports and i decided to make it a query based report since I will be pulling info from 2 tables (invoices and credits)
This is the query that I have
SELECT ObjType, DocDate, ItemCode, Dscription, SUM(Quantity) as 'Total Quantity', LineTotal, GroupNum, U_PymtMethod, ItmsGrpNam
FROM
( SELECT T0.[ObjType], T0.[DocDate], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[LineTotal], T4.[GroupNum], T4.[U_PymtMethod], T3.[ItmsGrpNam] FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode INNER JOIN OITB T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod INNER JOIN OCRD T4 ON T0.CardCode = T4.CardCode
UNION
SELECT T0.[ObjType], T0.[DocDate], T1.[ItemCode], T1.[Dscription], (T1.[Quantity]*-1), (T1.[LineTotal] * -1), T4.[GroupNum], T4.[U_PymtMethod], T3.[ItmsGrpNam] FROM ORIN T0 INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode INNER JOIN OITB T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod INNER JOIN OCRD T4 ON T0.CardCode = T4.CardCode )
InvoicesMinusCreditsTable
GROUP BY ObjType, DocDate, ItemCode, Dscription, LineTotal, GroupNum, U_PymtMethod, ItmsGrpNam
Thanks
