SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 CASE statements		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqlpal2009
Yak Posting Veteran

58 Posts
Posted - 05/07/2009 :  08:52:50  Show Profile  Reply with Quote
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 - 05/07/2009 :  08:59:12  Show Profile  Reply with Quote
This sounds like a business logic error. What if customers can have multiple orders and use different addresses?
Go to Top of Page

sqlpal2009
Yak Posting Veteran

58 Posts
Posted - 05/07/2009 :  09:22:16  Show Profile  Reply with Quote
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.
Go to Top of Page

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts
Posted - 05/07/2009 :  09:44:12  Show Profile  Reply with Quote
Post your DDL.
Go to Top of Page

sqlpal2009
Yak Posting Veteran

58 Posts
Posted - 05/07/2009 :  09:56:10  Show Profile  Reply with Quote 

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
Go to Top of Page

sqlpal2009
Yak Posting Veteran

58 Posts
Posted - 05/07/2009 :  15:58:36  Show Profile  Reply with Quote
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?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000