How to get a Range of Date Ranges? 1

[MrPlough69]

Hi All,

I have a table called Seasons, which contains a list of football seasons along with the date that they start and end. And a table called Transfers which contains a list of players along with the team that they moved from/to, and the date of the transfer:

DECLARE @Seasons TABLE( Id INT, Name VARCHAR(5)
	, StartDate SMALLDATETIME, EndDate SMALLDATETIME )

INSERT INTO @Seasons VALUES ( 1,	'95-96',	'1995-07-01', 	'1996-06-30' )
INSERT INTO @Seasons VALUES ( 2,	'96-97',	'1996-07-01', 	'1997-06-30' )
INSERT INTO @Seasons VALUES ( 3,	'97-98',	'1997-07-01', 	'1998-06-30' )
INSERT INTO @Seasons VALUES ( 4,	'98-99',	'1998-07-01', 	'1999-06-30' )
INSERT INTO @Seasons VALUES ( 5,	'99-00',	'1999-07-01', 	'2000-06-30' )
INSERT INTO @Seasons VALUES ( 6,	'00-01',	'2000-07-01', 	'2001-06-30' )
INSERT INTO @Seasons VALUES ( 7,	'01-02',	'2001-07-01', 	'2002-06-30' )
INSERT INTO @Seasons VALUES ( 8,	'02-03',	'2002-07-01', 	'2003-06-30' )
INSERT INTO @Seasons VALUES ( 9,	'03-04',	'2003-07-01', 	'2004-06-30' )
INSERT INTO @Seasons VALUES ( 10,	'04-05',	'2004-07-01', 	'2005-06-30' )
INSERT INTO @Seasons VALUES ( 11,	'05-06',	'2005-07-01', 	'2006-06-30' )
INSERT INTO @Seasons VALUES ( 12,	'06-07',	'2006-07-01', 	'2007-06-30' )
INSERT INTO @Seasons VALUES ( 13,	'07-08',	'2007-07-01', 	'2008-06-30' )
INSERT INTO @Seasons VALUES ( 14,	'08-09',	'2008-07-01',	'2009-06-30' ) 

DECLARE @Transfers TABLE( Id INT, PlayerId INT,	FromTeamId INT
	, ToTeamId INT, TransferDate SMALLDATETIME )

INSERT INTO @Transfers VALUES ( 2,	1,	1,	2,	'1995-09-12' )
INSERT INTO @Transfers VALUES ( 3,	1,	2,	3,	'2001-01-10' )
INSERT INTO @Transfers VALUES ( 4,	1,	3,	4,	'2001-01-20' )
INSERT INTO @Transfers VALUES ( 5,	1,	4,	5,	'2006-03-12' )

What I need to get out is a list of seasons from the first transfer to the current season, showing which team the player was in at that particular time. The list also needs to show duplicates.

An example of my desired output is:

Season TeamId
95-96  2
96-97  2
97-98  2
98-99  2
00-01  3
00-01  4
01-02  4
02-03  4
03-04  4
04-05  4
05-06  5
06-07  5
07-08  5
08-09  5

Many thanks in advance.

 

[MrPlough69]

The closest I've got up to now is the following, but I'm still struggling to fill in the gaps:

SELECT *
FROM @Seasons
LEFT JOIN @Transfers ON StartDate <= TransferDate 
	AND TransferDate <= EndDate
WHERE GETDATE() > StartDate
AND ( SELECT Min( TransferDate ) FROM @Transfers WHERE PlayerId = 1 ) < EndDate

 

[RiverGuy]

Give this a try. I tested it with a second player as well. Seems to work ok. Be sure to test it out under different scenarios, modify as needed. You might be able to make it more efficient as well. By the way, it would sure be easier to have a "roster" table for each season.

SELECT s.*, t.TransferDate, t.FromTeamID, t.ToTeamID, t.PlayerID
FROM @Seasons s
LEFT OUTER JOIN @Transfers t 
ON (t.TransferDate BETWEEN s.StartDate AND s.EndDate) 
OR (t.TransferDate <= s.EndDate 
	AND NOT EXISTS (SELECT 1 
					FROM @Transfers t2 
					WHERE t2.TransferDate <= s.EndDate AND t2.PlayerID = t.PlayerID AND t2.TransferDate > t.TransferDate))

 

[MrPlough69]

Hi RiverGuy,

That's exactly what I was looking for!

I had an inkling that a self join would be required, but just couldn't get my head around it.

Many thanks, and have a star.