Challenging SQL Puzzle - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Challenging SQL Puzzle

Author

Topic

Kapital123
Starting Member

31 Posts

Posted - 2014-03-01 : 07:02:28

Hello all,

I have a rather interesting problem that I cannot figure out. Its properly best I give an example of what I am after:

Table 1 (5 columns)

Year Interval Prod_Line_1 Prod_Line_2 Result
2012 1 A F F
2012 1 B G B
2012 1 C H H
2012 1 D I I
2012 1 E J E
2012 2 A G G
2012 2 B E E
2012 2 I H I

Table to Construct

Year Interval Prod_Line_1 Prod_Line_2 Result NEW_COLUMN
2012 1 A F F .
2012 1 B G B .
2012 1 C H H .
2012 1 D I I .
2012 1 E J E .
2012 2 A G G .
2012 2 B E E .
2012 2 I A I I

Okay so let me explain. The first table above is what I currently have. The most important information here is stored in the 'Result' column where 'Interval' = 1. What I would like to construct is the second table above which creates a new column whereby if the 'Result' column for 'Interval' = 1 exists in either Prod_Line_A or Prod_Line_B then to populate the 'new column' with the respective letter. So for example,see 'I'. If it doesn't exist, then just leave it empty e.g. see the row for interval '2' where A and G are not existent for the previous interval in the 'Result' column. There is one rule however, we can see that 'B' and 'I' exist in the 'Result' column for 'Interval' 1, however because they are lined up against each other for 'Interval' 2 I would like the new column to still generate a null. There needs to be mutual exclusivity.

Now this sounds harder than it is, but the idea is very simple. Its essentially like taking the previous week's result and comparing to the current week results. The only catch is that the products from the previous week have to exist in the current week, those that don't exist are irrelevant.

If anyone on here has the solution, I'd love to hear your thoughts. I'm sure there are some SQL tricks that I am unaware of that can solve this.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-01 : 08:00:17

i have read through your thread more than 10 times and still no clue what are you trying to achieve

quote:
if the 'Result' column for 'Interval' = 1 exists in either Prod_Line_A or Prod_Line_B then to populate the 'new column' with the respective letter.

What is the respective letter ? What i see you have here is dot ?

2012 1 A F F .
2012 1 B G B .
2012 1 C H H .
2012 1 D I I .
2012 1 E J E .

quote:
So for example,see 'I'. If it doesn't exist, then just leave it empty

Can you highlight when row you are referring to ?
i can see a "2012 1 D I I ." but the new column is dot not empty

quote:
e.g. see the row for interval '2' where A and G are not existent for the previous interval in the 'Result' column

what row are you referring to ? What previous interval ?

quote:
There is one rule however, we can see that 'B' and 'I' exist in the 'Result' column for 'Interval' 1, however because they are lined up against each other for 'Interval' 2 I would like the new column to still generate a null

whate do you mean by "lined up"

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-03-01 : 11:23:53

You alone knows what your rules. Elaborate it properly with sample data and then somebody will try to help.
see below to get guidelines on how to post a question.
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Kapital123
Starting Member

31 Posts

Posted - 2014-03-01 : 19:22:38

My apologies guys. I will try to use a different analogy to tell the story. Lets pretend we're looking at soccer matches where we record the round played, the home team, the away team and the loser of that match. So we will have 5 columns in the table:

Col1 - Year
Col2 - Round
Col3 - Home Team
Col4 - Away Team
Col5 - Loser

The idea: I want to place a bet on all the teams in round 2 that were losers in round 1. But sometimes the losers in round 1 won't play in round 2 because they have a bye. Also sometimes we may find that two losers from round 1 will play each other in round 2, I want to avoid that scenario also as it makes no sense to place a bet on both teams if they play each other.

So I want to somehow match up the round 2 matches with the losers from round 1. For example, in round 1 there was a match where Blues Vs Blacks and the Blues lost. In round 2, the Blues play the Reds. I want a new column alongside that tells me that Blues were the loser in round 1.

I hope that makes sense

Kapital123
Starting Member

31 Posts

Posted - 2014-03-01 : 19:48:13

In terms of the approach, perhaps some of you SQL guru's know a better approach. Sticking with the same sport analogy I just want to marry up the losers of the previous round to the games that their playing in round 2.

If I had to provide sample data it would be something like this:

Year Round Home_Team Away_Team Loser
2012 1 Reds Storm Storm
2012 1 Yellows Ravens Yellows
2012 1 Blues Blacks Blues
2012 1 Greens Lions Lions
2012 2 Reds Blues Reds
2012 2 Yellows Storm Storm

The new table I'd imagine would look like:

Year Round Home_Team Away_Team Loser Loser_Previous_Round
2012 1 Reds Storm Storm .
2012 1 Yellows Ravens Yellows .
2012 1 Blues Blacks Blues .
2012 1 Greens Lions Lions .
2012 2 Reds Blues Reds Blues
2012 2 Yellows Storm Storm .

NOTE: in the final row above, both the Yellows and Storm were losers in round 1 so I just leave the value null otherwise it wouldn't make sense.

Any questions please let me know. I keen to resolve this.

Table Two above has a new column

Col6 - Loser_Previous_Round (this is the one we hope to create)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-02 : 03:19:36

[code]
select *
from yourtable t
outer apply
(
select Loser_Previous_Round = max(x.Loser)
from yourtable x
where x.[Round] < t.[Round]
and (
x.Loser = t.Home_Team
or x.Loser = t.Away_Team
)
group by x.[Round]
having count(*) = 1
) lpr
[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-03-02 : 10:01:47

[code]
SELECT t.[Year],t.[Round],t.[Home_Team],t.[Away_Team],t.[Loser],t1.Loser AS Loser_Previous_Round
FROM
(
SELECT t.*,
COUNT(1) OVER (PARTITION BY t.[Year],t.[Round],t.[Home_Team],t.[Away_Team],t.[Loser]) AS Cnt
FROM YourTable t
LEFT JOIN YourTable t1
On (t1.Loser = t.Home_Team
OR t1.Loser = Away_Team)
AND t.[Year] = t1.[Year]
AND t.[Round] = t1.[Round] + 1
)t
WHERE cnt = 1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources