15,997,596 members
See more:
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
end as WeekStart
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```
Posted
Updated 17-Jan-17 8:18am

## Solution 1

I guess the simpliest way to solve your problem is to make use of the `DATEPART` function with parameter `wk` or `isowk` to get the week number of each date and group the summing by the resulting week number. See here for details about `DATEPART` function:
Monthly data to weekly data - SQL server query[^]

To get the dates for first and last day of a week you might use something like

```DATEADD(dd, -(DATEPART(dw, ADate)-1), ADate) [WeekStart]

You may also use this in conjunction with the `DATEPART` function to determine the weekdays of the first and last day in month.

Hope this helps...?

NightWizzard 17-Jan-17 14:20pm
Sorry, the link didn't work - here again the details for DATEPART:
https://msdn.microsoft.com/de-de/library/ms174420.aspx