Weekends between weekdays SQL

Question

I have a list of weekday's dates and want to insert rows for weekends/public holidays dates and populate data from previous row in SQL. Pls help.

Answer 1

Your holidays depend on what country you are in. This is the logic used for inserting two days after finding a Friday, similar logic can be applied if you know how to identify holidays, and how long they last.

create table #temp (fechas date, value int)



insert into #temp values ('20160601',2)
insert into #temp values ('20160602',4)
insert into #temp values ('20160603',8)
insert into #temp values ('20160606',2)
insert into #temp values ('20160607',1)


--TABLE
select *, DATEPART(DW,fechas) as dayOfTheWk 
into #temp2 
from #temp

-- selecting Fridays
declare @Fridays TABLE (fechas date,value int, dayOfTheWk int, nRow int)

insert into @Fridays
select *, DATEPART(DW,fechas) as dayOfTheWk, ROW_NUMBER() over(order by fechas) from #temp
where DATEPART(DW,fechas) = 6

declare @i int = 1, @maxI int

select @maxI = count(1) from @Fridays

while(@i <= @maxI)
begin 

        declare @x int = 1

        while (@x <= 2)
        begin 
            insert into #temp2 
            select 
                DATEADD(day,@x,fechas) as fechas, value, DATEPART(DW,DATEADD(day,@x,fechas))
            from @Fridays
            where nRow = @i 

        select @x += 1
        end 

select @i += 1
end 


select * from #temp2

fechas     value    dayOfTheWk
2016-06-01  2        4
2016-06-02  4        5
2016-06-03  8        6
2016-06-04  8        7
2016-06-05  8        1
2016-06-06  2        2
2016-06-07  1        3