Hi,
I am trying to create a query showing warehouse wise Sales with GP for each warehouse. This was resolved in an earlier question but I realised the sales numbers did not include AR Credit note. I tried adding ORIN/RIN1 to this query format but unsuccessful. Can anyone help to add the fix for CN?. Thanks.
SELECT
T1.WHSCODE AS "POS Code",T2.WHSNAME AS "POS Name" , SUM (T1.GTotal) AS "Gross Sales ( + IVA )", SUM (T1.LineTotal) AS 'Net Sales ( - IVA )', SUM (T1.LineVat) AS "IVA Value", SUM ( ( T1.LineVat)/ ( T1.LineTotal)) As "% IVA", SUM (T1.GPTtlBasPr) AS "Stocks Value", SUM (T1.GrssProfit) AS "Gross Profit", SUM(ISNULL(T1.GrssProfit,0))/SUM(ISNULL(T1.LineTotal,1) )*100 As "% GP", SUM (T0.DiscSum) AS "Discount",COUNT(T1.LineTotal) AS 'Sale In SKU', SUM (T1.quantity) AS 'Sale In Qty',SUM (T1.Weight1) AS "Sale In KG", SUM (T1.Volume) AS "Sale In Mtr"
FROM OINV T0
LEFT JOIN INV1 T1 ON T0.DOCENTRY = T1.DOCENTRY
LEFT JOIN OWHS T2 ON T1.WHSCODE = T2.WHSCODE
WHERE
T0.DocDate BETWEEN [%1] and [%2]
GROUP BY
T1.WHSCODE,T2.WHSNAME