Divide Monthly Working Hours into Weekly Hours - SQL Server

Question

I have a requirement where I have to generate a report using the below monthly data for each employee. The report runs on 1st day of every month and provides data till 1st of day of last month. The requirement is to divide this data in weekly data. So if 1st day of every month starts on "Monday" than the week should have 5 working days, "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. 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

Below is the expected results from the above query.

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

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.

Note: NULL represents that the employee didn't do the Swipe in/out correctly.

Sorry but i didn't tried anything myself as I am new to this kind of requirements and have very little experience in handling date & time queries.

I hope I have provided all the information and requesting all to contact me in case of any question or confusion.

iamdave · Answer 1 · 2017-01-16T14:03:38.950

I don't think you can actually have a time data type that goes above 24 hours, so I have broken your time worked down into separate fields for you to do what you want with. You could do away with the cte by just adding the big date case statements into your group by, but that would mean duplicated code and therefore duplicated effort to update it and I'm lazy:

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
            -- Pick the later of either the start of the current week or the current month.
        ,case when dateadd(wk, datediff(wk,0,WorkDate), 0) < dateadd(month,datediff(month,0,WorkDate),0)
                then dateadd(month,datediff(month,0,WorkDate),0)    -- This calculates the start of the month.
                else dateadd(wk, datediff(wk,0,WorkDate), 0)        -- This calculated the start of the week.
                end as WeekStart

            -- Pick the earlier of either the end of the current week or the current month.
        ,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))    -- This calculates the last day of the month.
                else dateadd(d,6,dateadd(wk, datediff(wk,0,WorkDate), 0))           -- This calculates the last day of the week.
                end as WeekEnd

            -- Pick the earlier of either the friday of the current week or or the end of the current month.
        ,case when dateadd(d,4,dateadd(wk, datediff(wk,0,WorkDate), 0)) > 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))    -- This calculates the last day of the month.
                                                                                else dateadd(d,6,dateadd(wk, datediff(wk,0,WorkDate), 0))           -- This calculates the last day of the week.
                                                                                end

                then 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))    -- This calculates the last day of the month.
                            else dateadd(d,6,dateadd(wk, datediff(wk,0,WorkDate), 0))           -- This calculates the last day of the week.
                            end
                else dateadd(d,4,dateadd(wk, datediff(wk,0,WorkDate), 0))           -- This calculates the Friday of the week.
                end as WorkingWeekEnd

        ,datepart(hour,WorkTime) as HoursWorked
        ,datepart(minute,WorkTime) as MinutesWorked
        ,datepart(second,WorkTime) as SecondsWorked
from @t
)
select EmpName
        ,WeekStart
        ,WeekEnd
        ,WorkingWeekEnd
        ,avg(datediff(d,WeekStart,WorkingWeekEnd)+1) * 8 as PlannedHoursWorked
        ,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
        ,WorkingWeekEnd
order by EmpName
        ,WeekStart;

Output:

╔═════════╦═════════════════════════╦═════════════════════════╦═════════════════════════╦════════════════════╦═════════════╦═══════════════╦═══════════════╗
║ EmpName ║        WeekStart        ║         WeekEnd         ║     WorkingWeekEnd      ║ PlannedHoursWorked ║ HoursWorked ║ MinutesWorked ║ SecondsWorked ║
╠═════════╬═════════════════════════╬═════════════════════════╬═════════════════════════╬════════════════════╬═════════════╬═══════════════╬═══════════════╣
║ User1   ║ 2016-10-17 00:00:00.000 ║ 2016-10-23 00:00:00.000 ║ 2016-10-21 00:00:00.000 ║                 40 ║          13 ║            41 ║            82 ║
║ User1   ║ 2016-10-24 00:00:00.000 ║ 2016-10-30 00:00:00.000 ║ 2016-10-28 00:00:00.000 ║                 40 ║          19 ║            89 ║            65 ║
║ User1   ║ 2016-10-31 00:00:00.000 ║ 2016-10-31 00:00:00.000 ║ 2016-10-31 00:00:00.000 ║                  8 ║           7 ║            49 ║            12 ║
║ User1   ║ 2016-11-01 00:00:00.000 ║ 2016-11-06 00:00:00.000 ║ 2016-11-04 00:00:00.000 ║                 32 ║           9 ║            23 ║             5 ║
║ User1   ║ 2016-11-07 00:00:00.000 ║ 2016-11-13 00:00:00.000 ║ 2016-11-11 00:00:00.000 ║                 40 ║          35 ║            39 ║            85 ║
║ User1   ║ 2016-11-14 00:00:00.000 ║ 2016-11-20 00:00:00.000 ║ 2016-11-18 00:00:00.000 ║                 40 ║           9 ║            18 ║            14 ║
╚═════════╩═════════════════════════╩═════════════════════════╩═════════════════════════╩════════════════════╩═════════════╩═══════════════╩═══════════════╝

Thanks for the response however the above is correct as per the scenario. If you can see the first row, between 17th Oct & 23rd Oct we have 5 working days(Mon to Fri) and the HoursPlanned should be 40(count(1)*8) but the results are showing 24 hours which is for 3 days only. I am looking for a solution which can help me calculate the weekly hours from 1st of Every month irrespective the day on that date. I hope this will help to understand more about the requirement. — ankur jain, Jan 16 '17 at 13:21

Divide Monthly Working Hours into Weekly Hours - SQL Server

1 Answers1