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!

[former_member541807]

hi,

try to run this code from your SQL management studio

SELECT     SUM(T0.DocTotal - T0.VatSum) AS [Order Value], T0.DocTotal - T0.VatSum AS [Order Value], T0.DocNum, T0.DocDate, T0.CardCode, T0.CardName, T0.DocTotal, 
                      T1.ItemCode, T1.Dscription, T1.Quantity, T2.GroupCode, T3.ItmsGrpCod
FROM         ODLN AS T0 INNER JOIN
                      DLN1 AS T1 ON T0.DocEntry = T1.DocEntry INNER JOIN
                      OCRD AS T2 ON T0.CardCode = T2.CardCode INNER JOIN
                      OITM AS T3 ON T1.ItemCode = T3.ItemCode
GROUP BY T0.DocTotal - T0.VatSum, T0.DocNum, T0.DocDate, T0.CardCode, T0.CardName, T0.DocTotal, T1.ItemCode, T1.Dscription, T1.Quantity, T2.GroupCode, 
                      T3.ItmsGrpCod
HAVING      (T2.GroupCode = 102 OR
                      T2.GroupCode = 107) AND (T3.ItmsGrpCod = 108 OR
                      T3.ItmsGrpCod = 112 OR
                      T3.ItmsGrpCod = 115) AND (T0.DocDate BETWEEN CONVERT(DATETIME, '2011-11-01 00:00:00', 102) AND CONVERT(DATETIME, '2011-11-17 00:00:00', 102))

regards,

Fidel

[Former Member]

Hi Fidel,

All that does is show each individual row. I'm trying to only get one record per DocNum/DocEntry as they are being returned multiple times (line items I'm guessing) each with an identical $ amount.

Any other ideas?

[former_member541807]

hi,

try this

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'
or (T3.[GroupCode] = '102' or T3.[GroupCode] = '107')
or (T2.[ItmsGrpCod] = '108' or T2.[ItmsGrpCod] = '112' or T2.[ItmsGrpCod] = '115')

regards,

Fidel

[Former Member]

Ouch! Now I have 385520 lines.

Essentially this should bring up a fraction of the now 1377 lines that are resulted from the query below

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')

I just can't get my head around the correct syntax

[Former Member]

Hi!

Try this,

SELECT  Docnum,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')
Group By
Docnum

[Former Member]

Sorry I should have been more specific.

The first query is the one I am having difficulty with. The query from my last post works fine until I add an additional join with OITM.