Get previous years holiday date
-
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?
HolidayDate HolidayName PreviousHolidayDate
1/1/2012 New Years Day NULL
1/16/2012 MLK Day NULL
2/20/2012 Presidents Day NULL
4/6/2012 Good Friday NULL
5/28/2012 Memorial Day NULL
7/4/2012 Independence DAY NULL
9/3/2012 Labor Day NULL
11/22/2012 Thanksgiving NULL
12/25/2012 Christmas Day NULL
1/1/2013 New Years Day 1/1/2012
1/21/2013 MLK Day 1/16/2012
2/18/2013 Presidents Day 2/20/2012
3/29/2013 Good Friday 4/6/2012
5/27/2013 Memorial Day 5/28/2012
7/4/2013 Independence Day 7/4/2012
9/2/2013 Labor Day 9/3/2012
11/28/2013 Thanksgiving 11/22/2012
12/25/2013 Christmas Day 12/25/2012
1/1/2014 New Years Day 1/1/2013
1/20/2014 MLK Day 1/21/2013
2/17/2014 Presidents Day 2/18/2013
4/18/2014 Good Friday 3/29/2013
5/26/2014 Memorial Day 5/27/2013
7/4/2014 Independence Day 7/4/2013
9/1/2014 Labor Day 9/2/2013
11/27/2014 Thanksgiving 11/28/2013
12/25/2014 Christmas Day 12/25/2013
-
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)
Intro to Tally Tables and Functions -
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
-
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
-
thanks guys.
running 2014 here and was not aware of LAG.... that is working perfectly.
Geoff A
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply