Need Help with the SQL

Question

Post reply

Need Help with the SQL

varunkum

Default port

Points: 1419
More actions
April 26, 2011 at 9:25 am

#239007

Hi guys i need your help with a SQL logic.
select account, address, addresstypeID from table1
There are around 10 addresstypeID's(starting from addresstypeID = 1 to 10) and addresses associated with each account. So for now i want addresses for all the accounts with AddresstypeID =8 as first priority and if that is empty or unavailable then second priority is AddresstypeID =1 and if that is empty or unavailable then third priority is AddresstypeID =3 and if that is also empty or unavailable then all teh remaining AddresstypeID in (2,4,5,6,7,9,10).
I want to add this Logic to the SQL. Please help me with the SQL.
I tried to come up with something. Can someone tell me if this does the JOB. I don't have a database to test at the moment. Please look at row over partition inside the with statement
With myCte (CustomerId,CustomerName,CustomerNumber, ContactName, Address1, Address2, City, StateTerritory, PostalCode, addressTypeId, rowId)
as
( Select c.customerId,c.CustomerName,c.CustomerNumber, a.ContactName, a.address1, a.address2, a.city, a.stateTerritory, a.PostalCode,
a.addressTypeId,
row_number() over (Partition by a.CustomerID
order by case when addressTypeId = 8 then 0
when addressTypeId = 1 then 1
when addressTypeId = 3 then 2
else 3 end ) as rowId
from AX.Customer c
inner join AX.CustomerAddress a on c.CustomerID = a.CustomerID
where a.Active = 'true'
and a.IsPrimary = 'true'
)
SELECTTOP (100) PERCENT
adr.ContactName,
adr.CustomerId,
adr.CustomerName,
adr.CustomerNumber,
adr.Address1,
adr.Address2,
adr.City,
adr.StateTerritory,
adr.PostalCode,
ad.Sales as SalesRep,
ad.SalesPersonNumber,
oln.ProductId
FROMdbo.vwRpt_Order ord(noLock)
INNER JOIN dbo.vwRpt_OrderLine oln(noLock)ON ord.OrderID = oln.OrderID
LEFT JOIN dbo.vwRpt_Ad ad(noLock)ON oln.OrderId = ad.orderId
And oln.OrderLineID = ad.OrderLineID
INNER JOIN dbo.vwRpt_Product vpr(noLock)ON oln.ProductID = vpr.ProductID
INNER JOIN Display.SectionVersion dsv(noLock)On ad.SectionVersionID = dsv.SectionVersionID
LEFT JOIN myCte adr(noLock)ON ord.SoldToCustomerId = adr.CustomerID
Where
oln.TearSheetCount > 0
and ISNULL(adr.rowId,1) = 1

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95405 More actions · Answer 1

;

WITH cteAddressIDOrder (AddressId, SortOrder) AS

(

SELECT 8, 1 UNION ALL

SELECT 1, 2 UNION ALL

SELECT 3, 3 UNION ALL

SELECT 2, 4 UNION ALL

SELECT 4, 5 UNION ALL

SELECT 5, 6 UNION ALL

SELECT 6, 7 UNION ALL

SELECT 7, 8 UNION ALL

SELECT 9, 9 UNION ALL

SELECT 10, 10

)

SELECT ...

JOIN cteAddressIDOrder c1

ON c1.AddressId = [SomeTable].AddressId

ORDER BY c1.SortOrder;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2