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