Sum value in periodic time

Question

I want sum work time of machine that save in table but I need sum in periodic time for example between 2019.07.10 and 2018.04.05 every 7 days sum values.

Answer 1

With a recursive CTE that returns all the 7 days intervals between the specified days:

declare @mindate date = '2018-04-05';
declare @maxdate date = '2019-07-10';
with cte as (
  select 
    @mindate startdate, 
    dateadd(day, 6, @mindate) enddate 
  union all 
  select 
    dateadd(day, 1, enddate),  
    case 
      when dateadd(day, 6, enddate) > @maxdate then @maxdate
      else dateadd(day, 6, enddate)
    end  
  from cte 
  where cte.enddate < @maxdate
)
select c.startdate, c.enddate, sum(hours) totalhours 
from cte c left join tablename t
on t.date between c.startdate and c.enddate
group by c.startdate, c.enddate

See a simplified demo.
Results:

> startdate  | enddate    | totalhours
> :----------| :--------- | ---------:
> 05/04/2018 | 11/04/2018 |         14
> ....................................
> 16/08/2018 | 21/08/2018 |         11
> ....................................
> 28/08/2018 | 02/09/2018 |          1
> ....................................