I can't get this summary work

I have this table that reflects the workhours per worker per day and possible incidences:

UserID    Date       Incidence   Workhours
user01    2019.4.1    1          0
user01    2019.4.2    0          8
user01    2019.4.3    2          0
user01    2019.4.4    1          0
user01    2019.4.5    1          0
user01    2019.4.8    1          0
user01    2019.4.9    1          0
user01    2019.4.10   0          8

For a given day @checkdate I want to get a summary of all people that are not working that day, the reason (incidence = holiday) for it, when their holiday started and until when it lasts. Example: if today was 2019.4.8 I want the query to return:

UserID    Incidence   From      To
user01    1           2019.4.4  2019.4.9

Only continous days with the same incidence have to be taken in account.

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL

CREATE TABLE #Temp (UserID Nvarchar(10),
		    Date DATETIME,
		    Incidence int,
		    Workhours int)

insert into #Temp 
select 'user01', '2019.4.1', 1, 0 union all
select 'user01', '2019.4.2', 0, 8 union all
select 'user01', '2019.4.3', 2, 0 union all
select 'user01', '2019.4.4', 1, 0 union all
select 'user01', '2019.4.5', 1, 0 union all
select 'user01', '2019.4.8', 1, 0 union all
select 'user01', '2019.4.9', 1, 0 union all
select 'user01', '2019.4.10', 0, 8

declare @Checkdate datetime = '2019.4.8'

select * from #temp 
   inner join #temp as ....
where date = @checkdate

This will look at records 30 days +/- from checkdate:

declare @checkdate datetime='2019-04-08';

with cte
  as (select userid
            ,sum(rn) over(partition by userid
                              order by [date]
                               rows unbounded preceding
             as rn
        from (select userid
                        when incidence=lag(incidence) over(partition by userid
                                                               order by [date]
                        then 0
                        else 1
                     end as rn
                from #temp
               where [date]>=dateadd(day,-30,@checkdate)
                 and [date]<dateadd(day,31,@checkdate)
             ) as a
select b.userid
      ,min(b.[date]) as [from]
      ,max(b.[date]) as [to]
  from cte as a
       inner join cte as b
               on b.userid=a.userid
              and b.rn=a.rn
 where a.[date]=@checkdate
 group by b.userid
1 Like

Great, but... I've been puzzling over this for hours and you solution works great but unfortunately not on the server it is supposed to run which is an express version 10.50. I will try to understand what you are doing and see if I can find a workaround for the missing functions.