SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Challenging SQL Puzzle
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kapital123
Starting Member

29 Posts

Posted - 03/01/2014 :  07:02:28  Show Profile  Reply with Quote
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)

Singapore
17595 Posts

Posted - 03/01/2014 :  08:00:17  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/01/2014 :  11:23:53  Show Profile  Reply with Quote
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

Edited by - visakh16 on 03/01/2014 11:42:58
Go to Top of Page

Kapital123
Starting Member

29 Posts

Posted - 03/01/2014 :  19:22:38  Show Profile  Reply with Quote
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


Go to Top of Page

Kapital123
Starting Member

29 Posts

Posted - 03/01/2014 :  19:48:13  Show Profile  Reply with Quote
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)


Edited by - Kapital123 on 03/01/2014 19:49:12
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17595 Posts

Posted - 03/02/2014 :  03:19:36  Show Profile  Reply with Quote

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



KH
Time is always against us

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/02/2014 :  10:01:47  Show Profile  Reply with Quote

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000