Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Holy Case Statements Batman!<!-- 864 --> Expand / Collapse
Author
Message
Posted Wednesday, August 20, 2014 4:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 12:24 PM
Points: 4, Visits: 57
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?
Post #1605630
Posted Wednesday, August 20, 2014 5:19 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 9:09 PM
Points: 20,739, Visits: 32,528
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1605633
Posted Wednesday, August 20, 2014 5:37 PM This worked for the OP Answer marked as solution
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 7:48 PM
Points: 1,057, Visits: 3,127
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;

Post #1605637
Posted Thursday, August 21, 2014 8:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 12:24 PM
Points: 4, Visits: 57
Brilliant mickyT!

This solved my problem! Thank you sooooooooo much!
Post #1605872
Posted Thursday, August 21, 2014 1:58 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 7:48 PM
Points: 1,057, Visits: 3,127
No problem, glad to help
Post #1605991
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse