Hi all,
I'll try to elaborate as much as possible so here goes:
I have a query that I am trying to run in SAP B1 8.8 which is not returning the true set of results I am expecting.
Essentially I am trying to filter by product based on delivery notes. I have the query as follows -
SELECT count(*), sum(T0.[DocTotal]-T0.[VatSum]) as "Order Value" FROM ODLN T0 INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode WHERE DateName(mm,T0.DocDate) = 'November' and DateName(yyyy,T0.DocDate) = '2011' and (T1.[GroupCode] = '102' or T1.[GroupCode] = '107')
This returns the correct result of 1320 row numbers and the given sum.
When I try to break this query down further by product using the query below, the values are doubled, tripled, quadrupled etc and summed up to give a number that is extremely high.
The code is as follows:
SELECT distinct count (*), sum(distinct T0.[DocTotal]-T0.[VatSum]) as "Order Value" from DLN1 T1 inner join ODLN T0 on T0.DocEntry = T1.DocEntry Inner join OITM T2 on T2.ItemCode = T1.ItemCode inner join OCRD T3 on T0.CardCode = T3.CardCode WHERE DateName(mm,T0.DocDate) = 'November' and DateName(yyyy,T0.DocDate) = '2011' and (T3.[GroupCode] = '102' or T3.[GroupCode] = '107') and (T2.[ItmsGrpCod] = '108' or T2.[ItmsGrpCod] = '112' or T2.[ItmsGrpCod] = '115')
This also returns 1819 rows where there should be less based on the product.
Please let me know if I am not explaining myself well or need further clarification.
Thanks!