August 20, 2014 at 4:27 pm
Here's the scenario... I have two tables.
1. OriginalItem
2. AlternateRecommendations
For each OriginalItem, I need to come up with up to eight alternate recommendations.
Currently my query looks something like:
Select
OriginalItem.ItemCode
,(Case When [BestMatch].[RecNumber]=1 Then [BestMatch].[AlternateItem] Else Null End) [Alt1]
(Case When [NestBestMatch].[RecNumber]=2 Then [NextBestMatch].[AlternateItem] Else Null End) [Alt2]
From OriginalItem
Left Join
(Select * From
(Select ItemCode As OriginalItem,
AlternateItem,
Row_Number() over (Partition by ItemCode Order by Date desc) as RecNumber
From AlternateRecommendations) As [RecommendationRank]
Where RecNumber = 1) As BestMatch
On BestMatch.OriginalItem = OriginalItem.ItemCode
Left Join
(Select * From
(Select ItemCode As OriginalItem,
AlternateItem,
Row_Number() over (Partition by ItemCode Order by Date desc) as RecNumber
From AlternateRecommendations) As [RecommendationRank]
Where RecNumber = 2) As NextBestMatch
On NextBestMatch.OriginalItem = OriginalItem.ItemCode
Obviously, this isn't very maintainable, I'd have to have eight left joins and eight case statements to come up with all necessary recommendations.
Any suggestions on a better solution?
August 20, 2014 at 5:19 pm
Not enough to work with here. If you could post the DDL (CREATE TABE statement) for the two tables, some sample data for the tables (INSERT INTO statements), and the expected results based on the sample data I am sure we can help you with your query.
If you need some help with the above information, please read the first article I have referenced below in my signature block. It will walk you through what you should post and how to post it to get the best possible answers.
August 20, 2014 at 5:37 pm
Hi
You could try
WITH RecommendationRank AS (
SELECT ItemCode As OriginalItem,
AlternateItem,
Row_Number() over (Partition by ItemCode Order by Date desc) as RecNumber
From AlternateRecommendations
)
SELECT
o.ItemCode
,MAX(CASE WHEN r.RecNumber = 1 THEN r.AlternateItem ELSE NULL END) Alt1
,MAX(CASE WHEN r.RecNumber = 2 THEN r.AlternateItem ELSE NULL END) Alt2
,MAX(CASE WHEN r.RecNumber = 3 THEN r.AlternateItem ELSE NULL END) Alt3
,MAX(CASE WHEN r.RecNumber = 4 THEN r.AlternateItem ELSE NULL END) Alt4
,MAX(CASE WHEN r.RecNumber = 5 THEN r.AlternateItem ELSE NULL END) Alt5
,MAX(CASE WHEN r.RecNumber = 6 THEN r.AlternateItem ELSE NULL END) Alt6
,MAX(CASE WHEN r.RecNumber = 7 THEN r.AlternateItem ELSE NULL END) Alt7
,MAX(CASE WHEN r.RecNumber = 8 THEN r.AlternateItem ELSE NULL END) Alt8
FROM OriginalItem o
CROSS APPLY (SELECT TOP 8 * FROM RecommendationRank r WHERE o.ItemCode = r.ItemCode ORDER BY r.RecNumber) a
GROUP BY o.ItemCode;
August 21, 2014 at 8:50 am
Brilliant mickyT!
This solved my problem! Thank you sooooooooo much!
August 21, 2014 at 1:58 pm
No problem, glad to help
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply