Pfthb

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;