Wrox Programmer Forums
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old May 23rd, 2008, 01:45 AM
Registered User
Join Date: May 2008
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Case help

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 ,
t3.shorted ,
t4.onhand ,
t6.cardname ,
WHEN t8.linestatus = 'O'
THEN t9.docnum
(SELECT t0.product product ,
WHEN t0.qty_topick <> t0.qty_picked
THEN t0.qty_topick - t0.qty_picked
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
(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 ,
t3.shorted ,
t4.onhand ,
ORDER BY t2.u_vlgx_plc,
t6.cardname ,

Similar Threads
Thread Thread Starter Forum Replies Last Post
update with case darkhalf Access 3 February 20th, 2008 12:00 AM
case switch thtrance BOOK: Beginning PHP4/PHP 5 ISBN: 978-0-7645-4364-7; v5 ISBN: 978-0-7645-5783-5 0 August 26th, 2004 02:36 AM
select case yuvalk SQL Server 2000 4 August 25th, 2004 02:33 PM
search string either Upper case or lower case rylemer Beginning VB 6 3 March 24th, 2004 04:23 PM
Check Case in a Case-Insensitive DB nbryson SQL Language 1 January 23rd, 2004 07:36 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.