April 29, 2011 at 9:17 am
I have a table table1 with CustomerID, OrderID, Address, AddressTypeID. So each CustomerID can have multiple AddressTypeID's. To elaborate this, each customer can have various addresses like home address, officeaddress, previous address...etc and each of them has a AddressTypeID(office, home..etc). There is another table table2 which has CustomerID, OrderID& Address.
Now i want address of customers. AddressTypeID = 17 as first priority, if a customer has Address for AddressTypeID = 17 then show(don't show other address types), if not
AddressTypeID = 1 as second priority, if a customer has Address for AddressTypeID = 1 then show(don't show other address types), if not
Priority 3, Go to table 2 fetch the address present there.
With myCte (CustomerId,CustomerName,CustomerNumber, Address, addressTypeId, rowId)
as
( Select c.customerId,c.CustomerName,c.CustomerNumber, c.address,
c.addressTypeId,
row_number() over (Partition by c..CustomerID
order by case when c.addressTypeId = 17 then 0
when c.addressTypeId = 1 then 1
else 2 end ) as rowId
from table1 c
)
SELECTCustomerId,CustomerName,CustomerNumber, Address, addressTypeId
FROM myCte
Where ISNULL(adr.rowId,1) = 1
The above SQL is not complete. Priority 3 is missing. It comes from a different table. SQl for that is below.
SELECTCustomerId,CustomerName,CustomerNumber, Address, addressTypeId
FROM table2
How do i put the Priority3 with the other priorities. You may change the whole SQL if you want. You don't have to stick on to rownumber() partition. Change it as you need. I need the output. Thanks.
April 29, 2011 at 9:39 am
How about some ddl and some readily consumable data? For instruction view the link in my signature.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 29, 2011 at 12:38 pm
This is the answer
Basically, the idea you put in the TypeRankValue column the priority of this address, so that might be something like
With cte As
(Select c.customerId,c.CustomerName,c.CustomerNumber, c.address, c.addressTypeId,
Case When c.addressTypeId = 17 Then 1
When c.addressTypeId = 1 Then 3
Else 4 End As TypeRankValue
From table1 c
Union All
Select c.customerId,c.CustomerName,c.CustomerNumber, c.address, c.addressTypeId,
2 As TypeRankValue
From table2 c),
cte2 As
(Select c.customerId,c.CustomerName,c.CustomerNumber, c.address, c.addressTypeId, c.TypeRankValue,
Rank() Over (Partition By c.CustomerID Order By c.TypeRankValue) As rk
From cte c)
Select c.customerId,c.CustomerName,c.CustomerNumber, c.address, c.addressTypeId
From cte2.c
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply