Case help - SQL Server Forums

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)

Case help

Author

Topic

michaelb
Yak Posting Veteran

69 Posts

Posted - 2008-05-22 : 23:46:15

Hi guys,

Bit of background on this query... daily report that tells me each product we sell that we were unable to supply to our customers.

What I need to include is the earliest unfulfilled purchase order and the date that purchase order is due for delivery into our warehouse.
I've used a case statement with MIN to get the earliest unfulfilled PO (t9.docnum), but I have been unable to get the due date (t9.docduedate) to show correctly... it just gives me the due dates of every unfulfilled purchase order, and doesn't link the purchase order returned by the case statement. Any ideas? Thanks!

SELECT t3.product ,
t7.itemname ,
t2.u_vlgx_plc,
t3.shorted ,
t4.onhand ,
t6.cardname ,
MIN(
CASE
WHEN t8.linestatus = 'O'
THEN t9.docnum
ELSE NULL
END) AS 'PO'
FROM
(SELECT t0.product product ,
SUM(
CASE
WHEN t0.qty_topick <> t0.qty_picked
THEN t0.qty_topick - t0.qty_picked
ELSE 0
END) shorted
FROM rbeacon.dbo.shipline2 t0
INNER JOIN rbeacon.dbo.shiphist t1
ON t0.packslip = t1.packslip
WHERE t1.date_upld = CONVERT(VARCHAR(10), GETDATE()-1, 101)
GROUP BY t0.product
) t3
INNER JOIN comparison.dbo.vlgxplc t2
ON t2.itemcode = t3.product COLLATE Latin1_General_CI_AS
LEFT JOIN
(SELECT t0.product AS product,
SUM(t0.quantity) AS onhand
FROM rbeacon.dbo.binlocat t0
GROUP BY t0.product
) t4
ON t3.product = t4.product
INNER JOIN wbau.dbo.oitm t5
ON t3.product = t5.itemcode COLLATE SQL_Latin1_General_CP850_CI_AS
LEFT JOIN wbau.dbo.ocrd t6
ON t5.cardcode = t6.cardcode
INNER JOIN wbau.dbo.oitm t7
ON t3.product = t7.itemcode COLLATE SQL_Latin1_General_CP850_CI_AS
LEFT JOIN wbau.dbo.por1 t8
ON t3.product = t8.itemcode COLLATE SQL_Latin1_General_CP850_CI_AS
LEFT JOIN wbau.dbo.opor t9
ON t8.docentry = t9.docentry

WHERE t3.shorted <> 0
GROUP BY t3.product ,
t7.itemname ,
t2.u_vlgx_plc,
t3.shorted ,
t4.onhand ,
t6.cardname
ORDER BY t2.u_vlgx_plc,
t6.cardname ,
t3.product

nduggan23
Starting Member

42 Posts

Posted - 2008-05-23 : 07:48:36

At a quick glance id say its the left outer join that is returning everything instead of just the orders you want to deal with in t9.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-23 : 07:52:03

Providing some sample data from your tables and your expected o/p out of them will really help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources