Add an additional flag value to the SQL query based on consecutive day logic

Question

I have a table with the following structure

Date            Holiday Flag     
12/23/2016      -1               
12/24/2016      -1               
12/25/2016       1               
12/26/2016       1               
12/27/2016      -1               

I want to add an additional flag based derived from the two columns mentioned above as such

Date            Holiday Flag     Previous Flag
12/23/2016      -1               -1
12/24/2016      -1               -1
12/25/2016       1               -1
12/26/2016       1                1
12/27/2016      -1               -1

Basically, in the event that there's a holiday on two consecutive days (12/25/2016 and 12/26/2016), I want 'Previous Flag' to reflect that on the second day (12/26/2016) as 1

I'm using SQL Server 2008 to form the query but cant seem to figure out the logic.

What is the best way to approach this situation? Thank you in advance for your help, I'm new to programming. Any help will be appreciated.

Answer 1

With the help of a CTE and Row_Number()

Declare @YourTable table (Date date, [Holiday Flag] int)
Insert Into @YourTable values
('12/23/2016',-1),
('12/24/2016',-1),               
('12/25/2016', 1),               
('12/26/2016', 1),               
('12/27/2016',-1)

;with cte as (
Select *
      ,RN = Row_Number() over (Order By Date)
 From  @YourTable
) 
Select A.Date
      ,A.[Holiday Flag]
      ,[Previous Flag] = IsNull(B.[Holiday Flag],A.[Holiday Flag])
 From  cte A
 Left Join cte B on (B.RN=A.RN-1)
 Order By A.Date

Returns

Not sure I agree with the desired results. I show 12/27 previous flag as 1

Answer 2

This might work.

DECLARE @T TABLE (Date DATETIME,HolidayFlag INT)
INSERT INTO @T SELECT '12/23/2016',-1
INSERT INTO @T SELECT '12/24/2016',-1
INSERT INTO @T SELECT '12/25/2016',1
INSERT INTO @T SELECT '12/26/2016',1
INSERT INTO @T SELECT '12/27/2016',-1

SELECT
    This.Date,
    This.HolidayFlag,
    LastHolidayFlag=CASE WHEN Last.Date IS NULL THEN -1 ELSE Last.HolidayFlag END
FROM
(
    SELECT Date,HolidayFlag,RowNumber=ROW_NUMBER() OVER (ORDER BY Date) FROM @T
)AS This
LEFT OUTER JOIN
(
    SELECT Date,HolidayFlag, RowNumber=ROW_NUMBER() OVER (ORDER BY Date)  FROM @T
)AS Last ON Last.RowNumber=This.RowNumber-1

Answer 3

Not too complicated. Try this.

Declare @Table table (Date date, [Holiday Flag] int)
Insert Into @Table values
('12/23/2016',-1),
('12/24/2016',-1),               
('12/25/2016',1),               
('12/26/2016',1),               
('12/27/2016',-1)

Select A.Date
      ,A.[Holiday Flag]
      ,[Previous Flag] = IsNull(B.[Holiday Flag],A.[Holiday Flag])
 From  @Table A
 Left Join @Table B on (DateAdd(day,-1 , A.Date)=B.Date)
 Order By A.Date

Answer 4

Since you have sequential dates, a ROW_NUMBER() is superfluous. Try this:

Declare @MyTbl table (
    Dt date PRIMARY KEY, 
    HolidayFlag int
    )

Insert Into @MyTbl 
values
    ('12/23/2016',-1),
    ('12/24/2016',-1),               
    ('12/25/2016', 1),               
    ('12/26/2016', 1),               
    ('12/27/2016',-1)

SELECT t1.Dt as [Date], 
    t1.HolidayFlag as [Holiday Flag],
    CASE
        WHEN t1.HolidayFlag = 1
            THEN IsNull(t2.HolidayFlag, -1)
        ELSE -1
    END as [Previous Flag]
FROM @MyTbl t1
LEFT JOIN @MyTbl t2
    ON t2.dt = dateadd(day, -1, t1.dt)