Agfdhyk

I have to add weekend and holiday's value to the previous working day value so that weekend and holiday's should not display in the report but if we don't have previous working day we should simply skip the row as 2018-01-01 skipped in the below output

**DAYS      VALUE** 

2018-01-01  10  Holiday-1

2018-01-02  20  

2018-01-03  30  

2018-01-04  40  

2018-01-05  50  

2018-01-06  60  Saturday

2018-01-07  70  Sunday

2018-01-08  80  

2018-01-09  90  

2018-01-10  100 Holiday-2

OUTPUT

2018-01-02  20  

2018-01-03  30  

2018-01-04  40  

2018-01-05  180 

2018-01-08  80  

2018-01-09  190 

I am trying with LEAD, LAG, DATEDIFF and in other ways but not getting any solution so please guys help he with this problem.

When there is a row in your Holidays calendar table (I will assume, that weekends are there too), you need to find the max date, prior the current one, for which there is no row in holidays table. Then group by this "real date" and sum the value. Something like this:

declare @t table([DAYS] date, [VALUE] int)

declare @Holidays table([DAYS] date, Note varchar(100))



insert into @t values

('2018-01-01',  10),

('2018-01-02',  20),

('2018-01-03',  30),

('2018-01-04',  40),

('2018-01-05',  50),

('2018-01-06',  60),

('2018-01-07',  70),

('2018-01-08',  80),

('2018-01-09',  90),

('2018-01-10',  100)



insert into @Holidays values

('2018-01-01',  'Holiday-1'),

('2018-01-06',  'Saturday'),

('2018-01-07',  'Sunday'),

('2018-01-10',  'Holiday-2')



;with cte as (

select 

    IIF(h1.[DAYS] is not null /* i.e. it is a holiday */,

         (select max([DAYS])

          from @t t2

          where t2.[DAYS] < t1.[DAYS] and not exists(select * from @Holidays h2 where h2.[DAYS] = t2.[DAYS])), t1.[DAYS]) as RealDate

    , t1.[VALUE]

from @t t1

left join @Holidays h1 on t1.DAYS = h1.[DAYS]

)

select

    RealDate

    , sum([VALUE]) as RealValue

from cte

where RealDate is not null

group by RealDate

You can do this with cumulative sums (to define groups) and aggregation. Define the groups as the number of non-holidays on or before a given day, then aggregate. This is the same value for a non-holiday followed by a holiday.

Then aggregate:

select max(days) as days, sum(value)

from (select t.*,

             sum(case when holiday is null then 1 else 0 end) over (order by days asc) as grp

      from t

     ) t

group by grp;

EDIT:

With a separate holidays table, you just need to add the join:

select max(days) as days, sum(value)

from (select t.*,

             sum(case when h.holiday is null then 1 else 0 end) over (order by t.days asc) as grp

      from t left join

           holidays h

           on t.days = h.date

     ) t

group by grp;