Solved

Conditional CASE statements

Posted on 2009-05-07
7
178 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 60

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 60

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

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

On a regular basis I get questions about slow RDP performance, RDP connection problems, strange errors and even BSOD, remote computers freezing or restarting after initiation of a remote session. In a lot of this cases the quick solutions made b…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

719 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