Solved

Conditional CASE statements

Posted on 2009-05-07
7
173 Views
Last Modified: 2013-11-29
I have to write a query to select a addressid based on the different criteria for each row. If the the addressid is at the Purchaseorder then use that. If it is not at the PO then use the one which is at the Quote. But remember one PO can be of multiple Quotes so the addressid of max Quote should go there.  If there is no addressid at PO or Quote then use Customer's default addressid.

Can any one tell me how to implement this in SQL?

Thanks,
-BK
0
Comment
Question by:BrookK
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24330620
You can join them and then do a COALESCE.
SELECT COALESCE(p.addressid, q.addressid, c.addressid)
FROM PurchaseOrder p
LEFT JOIN (
    SELECT poid
    , addressid
    , row_number() OVER (ORDER BY quoteid DESC) AS ranking
) q ON p.poid = q.poid AND q.ranking = 1
LEFT JOIN Customer c ON p.customerid = c.customerid

Open in new window

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24330624
Type-o -- you will need the table for quotes...
SELECT COALESCE(p.addressid, q.addressid, c.addressid)
FROM PurchaseOrder p
LEFT JOIN (
    SELECT poid
    , addressid
    , row_number() OVER (ORDER BY quoteid DESC) AS ranking
    FROM Quotes
) q ON p.poid = q.poid AND q.ranking = 1
LEFT JOIN Customer c ON p.customerid = c.customerid

Open in new window

0
 
LVL 9

Expert Comment

by:ezraa
ID: 24330649
I think a CASE statement is the way to go.  For help on case statements: http://www.google.com/search?rlz=1C1GGLS_en-USUS292US303&ie=UTF-8&q=sql+server+case

You need to break down the logic into pieces:

select addressid = CASE
   WHEN (statement that returns true if there is addressid at Purchaseorder )  THEN (select statement that returns addressid at Purchaseorder )
   WHEN (statement that returns true if there is addressid at Quote) THEN (select top 1 addressid at Quote order by  Quote (to get max quote) )
  ELSE (select Customer's default addressid)
END
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 24330683
Hello BrookK,

SELECT AddressID = CASE  WHEN EXISTS (Select 1 from PurchaseOrder p where c.CustomerID = p.CustomerID  ) THEN (Select AddressID from PurchaseOrder p where c.CustomerID = p.CustomerID  )
                         WHEN EXISTS (SELECT 1 FROM Quote q where c.CustomerID = q.CustomerID ) THEN (SELECT TOP 1 AddressID FROM Quote where c.CustomerID = q.CustomerID  ORDER BY QuoteID DESC )
                         ELSE  AddressID END
FROM Customer c                        
                         


Regards,

Aneesh
0
 

Author Comment

by:BrookK
ID: 24330699
This is an update to a table. I do not know how to write Customer in the CASE and max QuoteID if PO.AddressiD is NULL.
This is my code:
 
UPDATE IC
SET IC.POID = POLI.POID,
	IC.BillToAddressID = 
	CASE WHEN PO.BillingAddressID IS NULL
		 THEN QH.BillCustomerAddressID
	END,
	IC.ShipToAddressID = 
	CASE WHEN PO.ShippingAddressID IS NULL
		 THEN QH.ShipCustomerAddressID
	END
FROM #InvoiceConsolidation IC
LEFT OUTER JOIN POInfo PO
	ON PO.POID = POLI.POID
LEFT OUTER JOIN QuoteHeader QH
	ON QH.QuoteID = IC.QuoteID
LEFT OUTER JOIN Customer C
         ON C.CustomerID = IC.CustomerID

Open in new window

0
 

Author Comment

by:BrookK
ID: 24332119
Can someone tell me now how to modify my code to get this logic working?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24451721
Sorry, i didnt see the other question, getting lot of mails from EE :)
 

UPDATE IC
SET IC.POID = POLI.POID,
	IC.BillToAddressID = CASE WHEN PO.BillingAddressID IS NULL THEN QH.BillCustomerAddressID
	                          ELSE  PO.BillingAddressID	END,
	IC.ShipToAddressID = CASE WHEN PO.ShippingAddressID IS NULL THEN QH.ShipCustomerAddressID
	                          ELSE PO.ShippingAddressID END
FROM #InvoiceConsolidation IC
LEFT OUTER JOIN POInfo PO ON PO.POID = POLI.POID
LEFT OUTER JOIN QuoteHeader QH 	ON QH.QuoteID = IC.QuoteID
LEFT OUTER JOIN Customer C  ON C.CustomerID = IC.CustomerID

Open in new window

0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Know what services you can and cannot, should and should not combine on your server.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question