Get previous years holiday date

Question

Get previous years holiday date

Geoff A

SSChampion

Points: 11511
More actions
January 6, 2015 at 9:25 am

#299969

Given this table;
DECLARE @table TABLE (HolidayDate DATE, HolidayName NVARCHAR(50))
INSERT INTO @table
( HolidayDate, HolidayName )
VALUES ('2012-01-01','New Years Day'),
('2012-01-16', 'MLK Day'),
('2012-02-20', 'Presidents Day'),
('2012-04-06', 'Good Friday'),
('2012-05-28', 'Memorial Day'),
('2012-07-04','Independence DAY'),
('2012-09-03','Labor Day'),
('2012-11-22','Thanksgiving'),
('2012-12-25','Christmas Day'),
('2013-01-01','New Years Day'),
('2013-01-21','MLK Day'),
('2013-02-18','Presidents Day'),
('2013-03-29','Good Friday'),
('2013-05-27','Memorial Day'),
('2013-07-04','Independence Day'),
('2013-09-02','Labor Day'),
('2013-11-28','Thanksgiving'),
('2013-12-25','Christmas Day'),
('2014-01-01','New Years Day'),
('2014-01-20','MLK Day'),
('2014-02-17','Presidents Day'),
('2014-04-18','Good Friday'),
('2014-05-26','Memorial Day'),
('2014-07-04','Independence Day'),
('2014-09-01','Labor Day'),
('2014-11-27','Thanksgiving'),
('2014-12-25','Christmas Day')
How to get a result set that shows a new column called PreviousHolidayDate with the corresponding holidays last years date?
HolidayDateHolidayNamePreviousHolidayDate
1/1/2012New Years DayNULL
1/16/2012MLK DayNULL
2/20/2012Presidents DayNULL
4/6/2012Good FridayNULL
5/28/2012Memorial DayNULL
7/4/2012Independence DAYNULL
9/3/2012Labor DayNULL
11/22/2012ThanksgivingNULL
12/25/2012Christmas DayNULL
1/1/2013New Years Day1/1/2012
1/21/2013MLK Day1/16/2012
2/18/2013Presidents Day2/20/2012
3/29/2013Good Friday4/6/2012
5/27/2013Memorial Day5/28/2012
7/4/2013Independence Day7/4/2012
9/2/2013Labor Day9/3/2012
11/28/2013Thanksgiving11/22/2012
12/25/2013Christmas Day12/25/2012
1/1/2014New Years Day1/1/2013
1/20/2014MLK Day1/21/2013
2/17/2014Presidents Day2/18/2013
4/18/2014Good Friday3/29/2013
5/26/2014Memorial Day5/27/2013
7/4/2014Independence Day7/4/2013
9/1/2014Labor Day9/2/2013
11/27/2014Thanksgiving11/28/2013
12/25/2014Christmas Day12/25/2013

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

Self join, look for the MAX date less than the current date with the same holiday name.

{edit} The self join should probably be in the form of a correlated sub-query in either the SELECT list or in a CROSS APPLY.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mark Cowne One Orange Chip Points: 26944 More actions · Answer 2

You can use LAG

SELECT HolidayDate, HolidayName,

LAG (HolidayDate) OVER (PARTITION BY HolidayName ORDER BY HolidayDate ) AS PreviousHolidayDate

FROM @table

ORDER BY HolidayDate;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

rxm119528 SSCrazy Points: 2960 More actions · Answer 3

With out the Windows Function

;with CTE as

(select rownum = ROW_NUMBER() OVER (ORDER BY HolidayName,HolidayDate desc),

HolidayName,HolidayDate from @table)

SELECT

cte.HolidayDate,

CTE.HolidayName,

Previous.HolidayDate PreviousHolidayDate

FROM CTE

LEFT JOIN CTE Previous ON Previous.rownum = CTE.rownum + 1

and Previous.HolidayName = CTE.HolidayName

order by PreviousHolidayDate

GO

Geoff A SSChampion Points: 11511 More actions · Answer 4

thanks guys.

running 2014 here and was not aware of LAG.... that is working perfectly.

Geoff A