CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
RMu
RMu

Reputation: 869

Filtering consecutive dates in SQL Server

I would like to filter consecutive dates from my holiday table if any of the consecutive date is yesterday. Below is my code to get consecutive dates with row numbers. 

SELECT 
    RW = ROW_NUMBER() OVER( PARTITION BY GRP ORDER BY HolidayDate),
    HolidayDate
FROM
    (SELECT 
         HolidayDate, 
         DATEDIFF(Day, '1900-01-01', HolidayDate) - ROW_NUMBER() OVER (ORDER BY HolidayDate) AS GRP, 
         HolidayType
     FROM 
         Holiday) A
ORDER BY
    HolidayDate

For example, yesterday's date is 03/14/2017. I need to get data from the Holiday table if there are any consecutive holidays involving this date.

Output should be null if there is no match else it should display dates as below

03/12/2017
03/13/2017
03/14/2017

Upvotes: 1

Views: 174

Answers (1)

Giorgos Altanis
Giorgos Altanis

Reputation: 2760

Well, you are almost there!

Define "yesterday" as a parameter, since it is easy to find. 

CREATE TABLE Holiday(
    HolidayDate SMALLDATETIME PRIMARY KEY, 
    HolidayType VARCHAR(20)
);

INSERT INTO Holiday VALUES 
  ('2016-12-25', 'A'), ('2016-12-26', 'A'), ('2017-01-01', 'A'), 
  ('2017-04-12', 'A'), ('2017-04-13', 'A'), ('2017-04-14', 'A');

DECLARE @yesterday SMALLDATETIME;
SET @yesterday = '2017-04-13'; -- belongs to a group of consecutive holidays
-- SET @yesterday = '2017-01-01'; -- isolated holiday
-- SET @yesterday = '2017-02-10'; -- not a holiday

; WITH groups as 
(
SELECT 
    HolidayDate, 
    DATEDIFF(Day, '1900-01-01', HolidayDate) 
    - ROW_NUMBER() OVER (ORDER BY HolidayDate) AS GRP, 
    HolidayType
FROM Holiday
),
consecutive as (
    SELECT GRP FROM groups GROUP BY GRP HAVING COUNT(*) > 1
)
SELECT DISTINCT g2.HolidayDate, g2.HolidayType 
FROM consecutive c 
JOIN groups g1 ON c.GRP = g1.GRP 
JOIN groups g2 ON g1.GRP = g2.GRP
WHERE g1.HolidayDate = @yesterday;

Upvotes: 2

Related Questions