Custom query returns inaccurate number

[Former Member]

I'm using the following report for sales analysis. The report works fine except that the resulting numbers in the columns "Units 1st Qtr", "Units Prior Qtr", "Units This Year", and "Units Prior Year" are ten times larger than they should be.

The number returned for "Units This Year" should be 52, but the report returns 520. If the number should be 430 it is returning 4300.

I found that I can compensate for this by dividing the sum by 10 but I would like to know why this is happening.

SELECT
 T0.ItemCode, T0.ItemName, T0.U_SES_PageNo, T0.OnHand, T0.IsCommited, T0.OnOrder,
 (SELECT Price FROM ITM1 WHERE ItemCode = T1.ItemCode AND PriceList = 1) [UnitPrice],
 (SELECT Price FROM ITM1 WHERE ItemCode = T1.ItemCode AND PriceList = 2) [UnitCost],

 UTQ = case when datepart(qq,OINV.DocDate) = 04
             and datepart(yyyy,OINV.DocDate)=datepart(yyyy,GetDate())
       then INV1.Quantity else 0 end,

 UPQ = case when datepart(qq,OINV.DocDate) = 03
             and datepart(yyyy,OINV.DocDate)=datepart(yyyy,GetDate())
       then INV1.Quantity else 0 end,

 UTY = case when datepart(yyyy,OINV.DocDate) = datepart(yyyy,GetDate())
       then INV1.Quantity else 0 end,

 UPY = case when datepart(yyyy,OINV.DocDate) = datepart(yyyy,GetDate())-1
       then INV1.Quantity else 0 end

  INTO ##TEMP
  FROM OINV

 INNER JOIN INV1
    ON OINV.DocEntry = INV1.DocEntry

 RIGHT OUTER JOIN OITM T0
    ON INV1.ItemCode = T0.ItemCode

 RIGHT OUTER JOIN ITM1 T1
    ON INV1.ItemCode = T1.ItemCode

 WHERE T0.CardCode = '[%0]'
 ORDER BY T0.ItemCode

SELECT
 ItemCode AS 'Item Code',
 ItemName AS 'Description',
 U_SES_PageNo AS 'Page Number',
 CAST(OnHand AS INT) AS 'On Hand',
 CAST(IsCommited AS INT) AS 'Committed',
 CAST(OnOrder AS INT) AS 'On Order',
 CAST(SUM(UTQ) AS INT) AS 'Units 4th Qtr',
 CAST(SUM(UPQ) AS INT) AS 'Units 3rd Qtr',
 CAST(SUM(UTY) AS INT) AS 'Units This Year',
 CAST(SUM(UPY) AS INT) AS 'Units Prior Year',
 UnitCost AS 'Cost',
 UnitPrice AS 'Price'

FROM ##TEMP
   GROUP BY ItemCode, ItemName, U_SES_PageNo, OnHand, IsCommited, OnOrder, UnitCost, UnitPrice
   ORDER BY ItemCode

DROP TABLE ##TEMP

[Former Member]

Hi Steve ,

I have found the problem .It is your join from ITM1 that is triggering your issue . Please put Itemcode instead of XXX .

Try this for test :

SELECT

T0.ItemCode, T0.ItemName, T0.OnHand, T0.IsCommited, T0.OnOrder,

-- (SELECT Price FROM ITM1 WHERE ItemCode = T1.ItemCode AND PriceList = 1) [UnitPrice],

-- (SELECT Price FROM ITM1 WHERE ItemCode = T1.ItemCode AND PriceList = 2) [UnitCost],

UTQ = case when datepart(qq,OINV.DocDate) = 04

and datepart(yyyy,OINV.DocDate)=datepart(yyyy,GetDate())

then INV1.Quantity else 0 end,

UPQ = case when datepart(qq,OINV.DocDate) = 03

and datepart(yyyy,OINV.DocDate)=datepart(yyyy,GetDate())

then INV1.Quantity else 0 end,

UTY = case when datepart(yyyy,OINV.DocDate) = datepart(yyyy,GetDate())

then INV1.Quantity else 0 end,

UPY = case when datepart(yyyy,OINV.DocDate) = datepart(yyyy,GetDate())-1

then INV1.Quantity else 0 end

INTO ##TEMP

FROM OINV

INNER JOIN INV1

ON OINV.DocEntry = INV1.DocEntry

RIGHT OUTER JOIN OITM T0

ON INV1.ItemCode = T0.ItemCode

-- INNER JOIN ITM1 T1

-- ON INV1.ItemCode = T1.ItemCode

--

WHERE T0.ItemCode = 'XXXXX'

ORDER BY T0.ItemCode

SELECT

ItemCode AS 'Item Code',

ItemName AS 'Description',

-- U_SES_PageNo AS 'Page Number',

CAST(OnHand AS INT) AS 'On Hand',

CAST(IsCommited AS INT) AS 'Committed',

CAST(OnOrder AS INT) AS 'On Order',

CAST(SUM(UTQ) AS INT) AS 'Units 4th Qtr',

CAST(SUM(UPQ) AS INT) AS 'Units 3rd Qtr',

CAST(SUM(UTY) AS INT) AS 'Units This Year',

CAST(SUM(UPY) AS INT) AS 'Units Prior Year'

-- UnitCost AS 'Cost',

-- UnitPrice AS 'Price'

FROM ##TEMP

GROUP BY ItemCode, ItemName, OnHand, IsCommited, OnOrder

ORDER BY ItemCode

[Former Member]

This report is run by vendor. WHERE T0.ItemCode = 'XXXXX' will not work, as we need a listing of all items from that vendor. WHERE T0.CardCode = '(%0)' is there so we can select all items associated with whichever vendor is input. Is this wrong to do?

I edited the last JOIN from a RIGHT OUTER JOIN (as it was) to INNER JOIN (as you wrote) but the result is the same. Numbers are 10 times as large as they should be.

[Former Member]

Hi Steve ,

The reason is your join with ITM1 as I was trying to explain . Since you are capturing Unit Cost and Unit price and your ITM1 link is giving error as

The reason I gave you to look at with Item because we are working in two different database . You can verify first with using your item number and see whether the result is same or not .

So far when I test with the result I am coming with correct data as it is easy for me to identify.

<Select The item .. use drag and relate with Invoice > Calculate the quantity to see how much you have sold

used for testing only . >

Let me test with Vendor .

<OITM contains only preferred vendor . >

Thank you

Bishal

Edited by: Bishal Adhikari on May 14, 2009 1:51 PM

[Former Member]

Hi Steve ,

Since OITM captures only preferrod vendor ,I am not sure about correct result .

Try with this query :

SELECT T0.[ItemCode], T0.[Quantity] FROM PDN1 T0 INNER JOIN OPDN T1 ON T0.DocEntry = T1.DocEntry WHERE T0.[ItemCode] ='[%0]'

and T1.[CardCode] ='[%1]'

<Here I am using GRPO as reference . >

If total unit sold match with your vendor information ,then fine otherwise you have to keep looking for another vendor to find the rest of the item because item master holds only one prefferod vendor.

But if you like to see your reference from vendor point of view then you are fine .

I guess for overall solution - You need to fix or alter your logic to capture unit price and 10 ..

Or Since SAP provides with 10 default price list ,you can just divide it by 10 .If you have addone more price list then just divide by 11 .

Hope this concludes your overall curiosity

Thank you

Bishal

[Former Member]

Thank you everyone for your input! It makes sense that it is being multiplied by 10 since there are 10 pricelists. I will stick to dividing the sums for correct results.

[Former Member]

The reason behind this is the link with ITM1 table. You probably has exactly 10 Price lists so that you got 10 times for each record in your temporary table. It is fine to divide your result by 10. You may put that in the first part of your query.

[Former Member]

I have tested your query without your UDF, everything work fine as expected if add DISTINCT to your first SELECT.

Thanks,

Gordon

[Former Member]

Adding DISTINCT to the first SELECT did not fix it on my end. The numbers are inaccurate.

Instead of being ten times what they should be they are "near" what they should be. Where it was showing me 520 when it should be 52, using DISTINCT, it returns 50.

Won't using SELECT DISTINCT cause it to throw out duplicate Invoice quantities? i.e. If three different customers purchased 10 of a given item, it is only going to count 1 of the invoices instead of all three? Resulting in a quantity total of 10 instead of 30 ...

As I mentioned, I found that dividing the SUM by 10 corrected my report, I just do not understand why the report is returning numbers exactly 10 times bigger than they should be.

CAST(SUM(UTQ)/10 AS INT) AS 'Units 1st Qtr',
 CAST(SUM(UPQ)/10 AS INT) AS 'Units Prior Qtr',
 CAST(SUM(UTY)/10 AS INT) AS 'Units This Year',
 CAST(SUM(UPY)/10 AS INT) AS 'Units Prior Year',

Edited by: Steve Blake on May 14, 2009 12:08 PM

[Former Member]

Hi Steve,

Distinct won't solve your issue .I tested this query and I receive inaccurate results in units per year .

I am not sure why it needs to be divided by 10 but once you divide by 10 ,you receive correct result .

Hope this helps

Bishal

Edited by: Bishal Adhikari on May 14, 2009 12:13 PM

[Former Member]

Hi Steve ,

After looking into database ,I saw that same result is repeated ten times so that you are seeing that kind of result .

I am not sure at this point but looks like it is coming because of join .

Still looking into it .

Thank you

Bishal