I can't get this summary work

Transact-SQL
1

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
DROP TABLE #Temp

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
2

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

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

with cte
  as (select userid
            ,[date]
            ,sum(rn) over(partition by userid
                              order by [date]
                               rows unbounded preceding
                         )
             as rn
        from (select userid
                    ,[date]
                    ,case
                        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
3

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.