Solved

Conditional CASE statements

Posted on 2009-05-07
7
166 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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…
Welcome to my series of short tips on migrations. Whilst based on Microsoft migrations the same principles can be applied to any type of migration. My first tip Migration Tip #1 – Source Server Health can be found here: http://www.experts-exchang…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

896 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now