CASE statements - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

CASE statements

Author

Topic

sqlpal2009
Yak Posting Veteran

58 Posts

Posted - 2009-05-07 : 08:52:50

Hello there,

I have to insert addresses for each record based on the precedence. If the address is defined at the Order then use that address first. If it is not there then use the address at the maximum quote number level if it is not there then use it at the customer level.

I know I have to use CASE statment. Could anyone suggest me how to do it?

Thanks,
-S

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-05-07 : 08:59:12

This sounds like a business logic error. What if customers can have multiple orders and use different addresses?

sqlpal2009
Yak Posting Veteran

58 Posts

Posted - 2009-05-07 : 09:22:16

Each record would have only one Order # and one address. And if that Order does not have Address then use the quote level address where the quote is maximum because each order could have multiple quotes.

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-05-07 : 09:44:12

Post your DDL.

sqlpal2009
Yak Posting Veteran

58 Posts

Posted - 2009-05-07 : 09:56:10

[code]
UPDATE IC
SET IC.POID = POLI.POID,
IC.BillToAddressID =
CASE WHEN PO.BillingAddressID IS NULL
THEN ISNULL(QH.BillCustomerAddressID, 0)
ELSE PO.BillingAddressID END,
IC.ShipToAddressID =
CASE WHEN PO.ShippingAddressID IS NULL
THEN ISNULL(QH.ShipCustomerAddressID, 0)
ELSE PO.BillingAddressID END
FROM #InvoiceConsolidation IC
LEFT OUTER JOIN POLineItemDetails POLID
ON POLID.QuoteDetailsID = IC.QuoteDetailsID
LEFT OUTER JOIN POLineItems POLI
ON POLI.POLineItemID = POLID.POLineItemID
LEFT OUTER JOIN POInfo PO
ON PO.POID = POLI.POID
LEFT OUTER JOIN QuoteHeader QH
ON QH.QuoteID = IC.QuoteID
[/code]

sqlpal2009
Yak Posting Veteran

58 Posts

Posted - 2009-05-07 : 15:58:36

Can someone please tell me how to write the case statments based on the condition.

In the above example - if the address is not at the Order level then take it at the Quote level and if it is not there as well then take it from the customer level. But one order could be of multuple quotes so only the address of highest quote should appear. How should I do that in the SQL query?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources