Monthly data to weekly data - SQL server query

See more:

SQL

SQL-Server

time

Hi All,

I have a requirement where I have to generate a report using the below monthly data for each employee. The report runs every month(scheduled & adhocly) and provides data for last 1 month.
The requirement is to divide this data in weekly data. So if Day of report run falls on "Monday" than the week should have 5 working days, similarly "Tuesday" - 4 working days, "Wednesday" - 3 working days and so on.. And Calculate the worktime based on the days employee worked in the corresponding week(Monday to Sunday). If the number of weeks is varying every month than the report should show data accordingly for each week.

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

Expected Results:

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

Quote:

Note: Planned hours are calculated based on the number of weekdays. Means Mon-Fri, so 8 hours per day will give 40 hours for a 5 day week. However, the actual hours needs to be calculated for all 7 days so that if someone works on weekends than the actual can reflect accordingly for any extra hours than the planned hours.

And NULL in worktime represents that the employee came to office but didn't do the Swipe in/out correctly due to which the worktime was not calculated.

What I have tried:

Below is the query, I managed to create by using different sources but still not able to get correct results.

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;

Results:

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