每月数据到每周数据 - SQL服务器查询 [英] Monthly data to weekly data - SQL server query

问题描述

大家好，



我有一个要求，我必须使用以下每位员工的月度数据生成报告。该报告每月运行一次（计划和临时）并提供过去1个月的数据。

要求是将这些数据除以每周数据。因此，如果报告日运行在星期一比一周应该有5个工作日，类似星期二 - 4个工作日，星期三 - 3个工作日等等。并根据员工天数计算工作时间在相应的一周（周一至周日）工作。如果每周的周数变化，则报告应相应地显示每周的数据。

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

引用：

注意：计划的小时数是根据工作日的数量计算的。意味着周一至周五，因此每天8小时将为每周5天提供40小时。但是，需要计算所有7天的实际小时数，这样如果有人在周末工作，实际情况可以相应地反映任何额外时间而不是计划时间。



工作时间中的NULL表示员工上任但未正确执行刷卡操作，因此未计算工作时间。

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

推荐答案

我想解决问题的最简单方法是使用带参数的 DATEPART 函数 wk 或 isowk 获取每个日期的周数，并按结果周数对总和进行分组。有关 DATEPART 功能的详情，请参阅此处：

每月数据到每周数据 - SQL服务器查询 [ ^ ]



要获取一周的第一天和最后一天的日期你可以使用类似的东西

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]
DATEADD(dd, 7-(DATEPART(dw, ADate)), ADate) [WeekEnd]

您也可以将此与 DATEPART 功能结合使用，以确定月中第一天和最后一天的工作日。



希望这有帮助......？

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...?

这篇关于每月数据到每周数据 - SQL服务器查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！