Skip to Content
0
Former Member
Nov 12, 2009 at 11:35 PM

Custom report missing proper data

24 Views

I've written a custom purchasing report but we just encountered a problem. For any item which hasn't been sold yet, the price and cost don't appear in the report.

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

 FULL JOIN OITM T0
    ON INV1.ItemCode = T0.ItemCode

 FULL 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)/10 AS INT) AS 'Units 4th Qtr',
 CAST(SUM(UPQ)/10 AS INT) AS 'Units 3rd Qtr',
 CAST(SUM(UTY)/10 AS INT) AS 'Units This Year',
 CAST(SUM(UPY)/10 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

What am I missing to make the report display the Price and Cost for an item which hasn't appeared on an invoice?