Missing data in report -- Need help!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Missing data in report -- Need help!

Author

Topic

scream775
Starting Member

5 Posts

Posted - 2009-11-19 : 15:06:14

I'm not very familiar with SQL reporting. I know the basics but now I've been given the task of finding out why this query is not returning some proper data.

This query creates a list of items from our inventory system and adds up the number of units that appear on an invoice. It's basically a "how many of this item we sold in the first, second, third, or fourth quarter". It's filtered by the Vendor we buy the item from. So the list of items would be filtered by vendor -- Show only the items we buy from vendor XXXX.

The problem is that any item which hasn't appeared on an invoice, the price and cost are not being displayed in the report.

http://img35.imageshack.us/img35/95/purchrep1.gif
You can see in that screenshot that the items which don't have any data in the "units prior" columns, the price and cost do not show.

My task is to get the price and cost to appear.

Here is my query which is being done in SAP Business One:

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

Item data is stored in the table OITM
Cost and price are stored in ITM1
How many of each item has been sold is stored in OINV and INV1
Vendor (CardCode) is stored in OITM

I'm pretty sure the tables are not joined properly, but I am unclear on what the proper way would be to join them while still being able to select the numbers sold data, as well as being able to filter it by vendor.

Any help is much appreciated!

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2009-11-20 : 13:08:54

Hard to from me to comment on the joins without me understanding the whole system.

- First write a select to verify in ITM1 that the prices do exist
- Try with other Vendors
- Try running the select only part of the select-insert to see what it returns

Just some ideas towards isolating the problem.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources