Ежемесячные данные в еженедельные данные-запрос SQL server

---

Всем Привет,

У меня есть требование, когда я должен сгенерировать отчет, используя приведенные ниже ежемесячные данные для каждого сотрудника. Отчет запускается каждый месяц (по расписанию и adhocly) и предоставляет данные за последний 1 месяц.
Требование состоит в том, чтобы разделить эти данные на еженедельные данные. Так, если день выполнения отчета приходится на" понедельник", то неделя должна иметь 5 рабочих дней, аналогично" вторник " - 4 рабочих дня," Среда " - 3 рабочих дня и так далее. И рассчитайте рабочее время на основе дней, отработанных сотрудником в соответствующую неделю (с понедельника по воскресенье). Если количество недель меняется каждый месяц, то отчет должен показывать соответствующие данные за каждую неделю.

EmpName Date        WorkTime
User1   2016-10-18  NULL
User1   2016-10-20  06:00:38
User1   2016-10-21  07:41:44
User1   2016-10-24  06:35:53
User1   2016-10-25  06:29:03
User1   2016-10-26  07:25:09
User1   2016-10-31  07:49:12
User1   2016-11-03  09:23:05
User1   2016-11-05  NULL
User1   2016-11-07  09:18:38
User1   2016-11-08  09:16:01
User1   2016-11-09  08:05:03
User1   2016-11-11  09:00:43
User1   2016-11-16  09:18:14

предполагаемые результаты:

WeekNum WeekDur         EmpName Planned     Actual
Week1   18/10 - 22/10   User1   32:00:00    13:42:22
Week2   23/10 - 29/10   User1   40:00:00    20:30:05
Week3   30/10 - 31/10   User1   8:00:00     7:49:12

Цитата:

Примечание: запланированные часы рассчитываются исходя из количества рабочих дней. Значит Пн-Пт, значит 8 часов в день дадут 40 часов на 5-дневную неделю. Однако фактические часы должны быть рассчитаны для всех 7 дней, чтобы, если кто-то работает в выходные дни, чем фактические, можно было соответственно отразить для любых дополнительных часов, чем запланированные часы.

А NULL в рабочем времени означает, что сотрудник пришел в офис, но не сделал свайп in/out правильно, из-за чего рабочее время не было рассчитано.

Что я уже пробовал:

Ниже приведен запрос, который мне удалось создать, используя различные источники, но все еще не удалось получить правильные результаты.

declare @t table (EmpName nvarchar(10), WorkDate date, WorkTime time);
insert into @t values
 ('User1','20161018',NULL),('User1','20161020','06:00:38'),('User1','20161021','07:41:44'),('User1','20161024','06:35:53'),('User1','20161025','06:29:03'),('User1','20161026','07:25:09'),('User1','20161031','07:49:12'),('User1','20161103','09:23:05'),('User1','20161105',NULL),('User1','20161107','09:18:38'),('User1','20161108','09:16:01'),('User1','20161109','08:05:03'),('User1','20161111','09:00:43'),('User1','20161116','09:18:14');

with cte as
(
select EmpName
        ,case when dateadd(wk, datediff(wk,0,WorkDate), 0) < dateadd(month,datediff(month,0,WorkDate),0)
                then dateadd(month,datediff(month,0,WorkDate),0)
                else dateadd(wk, datediff(wk,0,WorkDate), 0)
                end as WeekStart
        ,case when dateadd(d,6,dateadd(wk, datediff(wk,0,WorkDate), 0)) >= dateadd(month,datediff(month,0,WorkDate)+1,0)
                then dateadd(d,-1,dateadd(month,datediff(month,0,WorkDate)+1,0))
                else dateadd(d,6,dateadd(wk, datediff(wk,0,WorkDate), 0))
                end as WeekEnd
        ,datepart(hour,WorkTime) as HoursWorked
        ,datepart(minute,WorkTime) as MinutesWorked
        ,datepart(second,WorkTime) as SecondsWorked
from @t
)
select EmpName
        ,WeekStart
        ,WeekEnd
        ,count(1) * 8 as HoursPlanned
        ,isnull(sum(HoursWorked),0) as HoursWorked
        ,isnull(sum(MinutesWorked),0) as MinutesWorked
        ,isnull(sum(SecondsWorked),0) as SecondsWorked
from cte
group by EmpName
        ,WeekStart
        ,WeekEnd
order by EmpName
        ,WeekStart;

Результаты:

EmpName	WeekStart	WeekEnd	HoursPlanned	HoursWorked	MinutesWorked	SecondsWorked
User1	10/17/2016	10/23/2016	24	13	41	82
User1	10/24/2016	10/30/2016	24	19	89	65
User1	10/31/2016	10/31/2016	8	7	49	12
User1	11/1/2016	11/6/2016	16	9	23	5
User1	11/7/2016	11/13/2016	32	35	39	85
User1	11/14/2016	11/20/2016	8	9	18	14

---