Count for each day - the number of days until the closer workday. It is possible to limit the number of days to look ahead by 20 days.

data for query:

create table #tmp ( [date] date, is_holiday int )
insert into #tmp ( date, is_holiday )
select '2008-01-05' date, 1 is_holiday union 
select '2008-01-06' date, 1 is_holiday union 
select '2008-01-07' date, 1 is_holiday union 
select '2008-01-08' date, 1 is_holiday union 
select '2008-01-09' date, 0 is_holiday union 
select '2008-01-10' date, 0 is_holiday union 
select '2008-01-11' date, 0 is_holiday union 
select '2008-01-12' date, 1 is_holiday union 
select '2008-01-13' date, 1 is_holiday union 
select '2008-01-14' date, 0 is_holiday union 
select '2008-01-15' date, 0 is_holiday union 
select '2008-01-16' date, 0 is_holiday union 
select '2008-01-17' date, 0 is_holiday 

I've tried to use the construction like:

select date, sum(convert(int, is_holiday)) over (
    order by date
    rows between 5 preceding and current row
) as rsum
from dic_calendar_production

But it looks behind. When I change 'preceding' on 'following', it throws the error

'BETWEEN ... FOLLOWING AND CURRENT ROW' is not a valid window frame and cannot be used with the OVER clause.

Moreover, it can give me the sum in the some range, but it will not stop when it reach the first zero.

CodePudding user response：

Seems like you just need to put your data into groups, and then ROW_NUMBER:

create table #tmp ( [date] date, is_holiday int )
insert into #tmp ( date, is_holiday )
VALUES ('2008-01-05', 1),
       ('2008-01-06', 1),
       ('2008-01-07', 1),
       ('2008-01-08', 1),
       ('2008-01-09', 0),
       ('2008-01-10', 0),
       ('2008-01-11', 0),
       ('2008-01-12', 1),
       ('2008-01-13', 1),
       ('2008-01-14', 0),
       ('2008-01-15', 0),
       ('2008-01-16', 0),
       ('2008-01-17', 0);
GO
WITH CTE AS(
    SELECT [date],
           is_holiday,
           COUNT(CASE is_holiday WHEN 0 THEN 1 END) OVER (ORDER BY [date] ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS Grp
    FROM #tmp t)
SELECT [date],
       is_holiday,
       ROW_NUMBER() OVER (PARTITION BY Grp ORDER BY [date] DESC) AS desirableresult
FROM CTE
ORDER BY [date];

GO
DROP TABLE #tmp;

CodePudding user response：

You could use a correlated query with datediff:

select *, 
  IsNull(
    DateDiff(
    day, t.[date], 
      (select Min([date]) from #tmp t2 where t2.[date] > t.[date] and t2.is_holiday = 0)
  ),1) Result
from #tmp t
order by [date];

DB<>Fiddle example

CodePudding user response：

select t.date,t.is_holiday,isnull(app.date,t.date),isnull(DATEDIFF(day,t.date,app.date),1)diff
from #tmp t
cross apply
(
  select min(x.date) as date 
  from #tmp as x
  where t.date<x.date
 and x.is_holiday=0
)app

You can also try cross apply-approach