SAP query returning duplicate records

[Former Member]

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!

[Former Member · Accepted solution]

Hi,

Try:

SELECT count (*) Count, sum(T1.LineTotal) as "Order Value" from DLN1 T1

inner join ODLN T0 on T0.DocEntry = T1.DocEntry

Inner join OITM T2 on T2.ItemCode = T1.ItemCode and T2.ItmsGrpCod in ('108','112','115')

inner join OCRD T3 on T0.CardCode = T3.CardCode and T3.GroupCode in ('102','107')

WHERE DateDiff(mm,T0.DocDate,GetDate()) = 0

and DateDiff(yy,T0.DocDate,GetDate()) = 0

Thanks,

Gordon

[Former Member · Accepted solution]

Hi Gordon,

That seemed to do the trick.

However, it now seems the line total doesn't match the Document Total. What's even more strange is that it is only for a few particular group codes.

Have you come across this before?

[Former Member · Accepted solution]

That would be due to freight or discount. Check them out and add that field.

[Former Member · Accepted solution]

Hi Gordon,

Actually after a bit of further investigation, it seems that where a discount is applied per line item, it is summed.

This bit of code actually works, but I'm drawing blanks now how to combine/sum it into one line.

SELECT sum(T1.LineTotal)-T0.DiscSum as "Order Value" from DLN1 T1
inner join ODLN T0 on T0.DocEntry = T1.DocEntry
Inner join OITM T2 on T2.ItemCode = T1.ItemCode --and T2.ItmsGrpCod in ('108','112','115')
inner join OCRD T3 on T0.CardCode = T3.CardCode and T3.GroupCode in ('103')
WHERE DateDiff(mm,T0.DocDate,GetDate()) = 0
and DateDiff(yy,T0.DocDate,GetDate()) = 0
group by t0.DiscSum 

Edited by: Non Invasive on Nov 21, 2011 12:52 AM

[Former Member · Accepted solution]

Try:

SELECT T1.ItemCode,sum(T1.LineTotal(1-IsNull(T0.DiscPrcnt,0)0.01)) as "Order Value" from DLN1 T1

inner join ODLN T0 on T0.DocEntry = T1.DocEntry

Inner join OITM T2 on T2.ItemCode = T1.ItemCode --and T2.ItmsGrpCod in ('108','112','115')

inner join OCRD T3 on T0.CardCode = T3.CardCode and T3.GroupCode in ('103')

WHERE DateDiff(mm,T0.DocDate,GetDate()) = 0

and DateDiff(yy,T0.DocDate,GetDate()) = 0

group by T1.ItemCode

[Former Member · Accepted solution]

Thanks Gordon.

Sorry but I actually meant I'm trying to sum tall rows of the result into one.

Is that possible or should I just create a function in Crystal?

[Former Member · Accepted solution]

Try:

SELECT sum(T1.LineTotal(1-IsNull(T0.DiscPrcnt,0)0.01)) as "Order Value" from DLN1 T1

inner join ODLN T0 on T0.DocEntry = T1.DocEntry

Inner join OITM T2 on T2.ItemCode = T1.ItemCode and T2.ItmsGrpCod in ('108','112','115')

inner join OCRD T3 on T0.CardCode = T3.CardCode and T3.GroupCode in ('103')

WHERE DateDiff(mm,T0.DocDate,GetDate()) = 0

and DateDiff(yy,T0.DocDate,GetDate()) = 0

[Former Member · Accepted solution]

Thanks Gordon, that did the trick!