Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Custom query returns inaccurate number

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
Not what you were looking for? View more on this topic or Ask a question