我有一个每周工作时间的表，我需要按月报告数据。在大多数情况下，一周的开始日期和结束日期位于同一个月，这些记录很容易汇总，但当一周的开始日期和结束日期不在同一个月时，我需要将该周的记录分成2行。一个以实际开始日期和月底作为结束日期，另一个记录为下个月1日作为开始日期和实际结束日期作为结束日期。对于小时数的计算，我需要计算第一个月的天数，然后将总小时数除以5，然后乘以天数。对于第二条记录，它将是总HRS乘以5，然后乘以(5天数)
SELECT [USERNAME]
,[EMPLOYEE_NAME]
,[EFFORT_HRS]
,[TS_START_DATE]
,[TS_END_DATE]
FROM [dbo].[Source]
输出:
您没有提供的是与假期相关的某种日历，例如2021年1月1日(星期五)和2021年1月2日(星期六)，这些假期似乎没有支付。话虽如此，我将让您提供额外的信息来排除这些日期，或者弄清楚如何调整查询。
我还会建议一个更好的存储时间的解决方案，以每天为基础，更容易查询和包含/排除，如假期。但是，根据给定的内容，我有下面的示例来创建和填充每个示例数据的表。
create table Source
( UserName nvarchar(5),
Employee_Name nvarchar(5),
Task nvarchar(10),
Effort_Hrs numeric( 5, 2),
TS_Start_Date datetime,
TS_End_Date datetime )
insert into Source
( UserName,
Employee_Name,
Task,
Effort_Hrs,
TS_Start_Date,
TS_End_Date)
values
( 'mk', 'xyz', 'abcdefg', 40, '2021-12-27', '2022-01-02' ),
( 'mk',' xyz', 'defgh', 33.5, '2022-01-31', '2022-02-06' ),
( 'mk',' xyz', 'abcdefg', 6, '2022-04-25', '2022-05-01' )
现在是查询本身。无论基于现有数据做什么，都需要UNION。基本上，在每个查询中选择相同的数据列。首先，一次获取所有记录，但如果开始日期和结束日期不同，则将结束日期剪到该月的最后一个月。
UNION部分将只考虑开始日期和结束日期不同月份的记录。所以，如果你的工作周是1月5日到1月11日，你只会在没有分割的单个记录中看到它，但在其他跨月的例子中，你会得到两个记录。第一个月一个，第二个月一个。
我根据你工作日的反馈重写了查询，忽略了周末的反馈。因此，在计算挣得的工资时，我必须应用一个case/when if PayDays = 0，只返回0小时，否则你会得到一个除以0的错误
我正在添加额外的列(您可以删除)，因此您可以看到这些部分如何/在哪里发挥作用。现在，对于所有记录，开始日期是最终输出中开始日期的实际基础。在第一个查询中，很简单，它是开始日期。但是，在UNION部分查询中，开始日期是一个月的第一天，但是同样，只有在第二个查询中，月份是不同的。因此，我所做的日期加(净减)比结束日期所在月份的天数少1。
我还更改为使用EOMONTH()调用来计算给定日期的月底，例如在联合的第一部分中，结束日期与下一个月交叉，而不是最初使用的dateadd()。
对于查询的UNION部分中的支付日，它只表示跨到下一个月的条目，PayDays是结束日期days。所以2月6日是6天。
NOT month( ts_start_Date ) = month( ts_end_Date )
这是一个函数，用于计算给定时间段内的工作日开始/结束日期，强制通过每个单独的一天来确定星期一到星期五。
CREATE Function dbo.WorkDaysInWeek
( @pStartDate as datetime,
@pEndDate as datetime)
RETURNS int
AS
BEGIN
-- if bad start/end date because they passed in end date first, swap them
if( @pStartDate > @pEndDate )
begin
declare @holdDate as DateTime
set @holdDate = @pStartDate
set @pStartDate = @pEndDate
set @pEndDate = @holdDate
end
-- convert to just date to prevent false calculations on time consideration
set @pStartDate = convert( date, @pStartDate)
set @pEndDate = convert( date, @pEndDate )
declare @workDays as int
set @workDays = 0
WHILE ( @pStartDate <= @pEndDate)
BEGIN
-- is the current day being tested a week day vs weekend. Only count Mon-Fri
if( datepart( weekday, @pStartDate) in ( 2, 3, 4, 5, 6 ))
set @workDays = @workDays + 1
set @pStartDate = dateadd( day, 1, @pStartDate )
END
RETURN @workDays
end
GO
现在，不是以天为单位计算日期，而是用开始和结束日期调用函数，函数将分别循环每一天，以确定只计算星期一到星期五
这是最后一个查询。
select
AllWork.*,
case when AllWork.PayDays = 0
then 0.0
else ( AllWork.EFFORT_HRS / ( 1.0 * dbo.WorkDaysInWeek( AllWork.TS_Start_Date, AllWork.TS_End_Date))) * AllWork.PayDays end HoursInPayPeriod
from
(SELECT
USERNAME,
EMPLOYEE_NAME,
EFFORT_HRS,
TS_START_DATE,
TS_END_DATE,
TS_START_DATE as RealStart,
case when month( ts_start_Date ) = month( ts_end_Date )
then ts_end_date
-- simplified date add to use build-in function EOMONTH (End of Month)
else EOMONTH( ts_start_Date, 0) end RealEnd,
dbo.WorkDaysInWeek( TS_START_DATE,
case when month( ts_start_Date ) = month( ts_end_Date )
then ts_end_date
-- simplified date add to use build-in function EOMONTH (End of Month)
else EOMONTH( ts_start_Date, 0) end ) PayDays
FROM
Source
UNION
-- union to get all entries where the end date is a new month from the start
SELECT
USERNAME,
EMPLOYEE_NAME,
EFFORT_HRS,
TS_START_DATE,
TS_END_DATE,
dateadd( day, 1 - datepart( day, ts_end_Date ), ts_end_Date ) RealStart,
ts_end_date RealEnd,
dbo.WorkDaysInWeek( dateadd( day, 1 - datepart( day, ts_end_Date ), ts_end_Date ),
ts_end_date ) PayDays
FROM
Source
where
-- only care about those entries where the start and end date are different months
NOT month( ts_start_Date ) = month( ts_end_Date )
) AllWork