如何基于日期 - 每两周一次聚合SQL数据库中的行 [英] How to aggregate rows in SQL database based on date - fortnightly basis
问题描述
尊敬的专家，
我在数据库中有以下类型的行结构;
三列 - 用户ID |日期|工作时间
为每个用户填写数据（带有ID），每个日期（2010年1月至2020年12月）和每天的工作时间。
现在，业务流程是用户可以更改工作时间。例如，我通常每两周工作75小时。但是，我想将我的时间从2017年1月改为2017年3月，因为每两周只工作40小时。在这种情况下，系统将仅在这些日期之间编辑小时，剩余小时将保留至2020年12月。
现在我想退出关于我在组织中工作多少小时的报告。由于动态性和随时更改小时的业务流程，我希望得到如下输出;
测试用户01/01/2010至31 / 12/2016每周75小时
测试用户01/01/2017至2017年3月31日每两周40小时
测试用户01/04/2017至31 / 12/2020每周75小时
有谁可以帮我这个？
谢谢。
我尝试了什么：
我尝试过使用游标特定员工并使用向日期添加到日期并比较前两个小时，以查看是否存在按输出要求划分行的差异。但我很困惑，因为它没有达到目的。
请帮帮我。
谢谢。
您可以尝试以下代码：
选择
（选择 MIN（frmDate。日期）来自 WorkingLogs frmDate < span class =code-keyword>其中 frmDate.WorkingHours = wh.WorkingHours）[ From Date ]，
（选择 MAX（toDate。日期）从 WorkingLogs toDate 其中 toDate.WorkingHours = wh.WorkingHours）[ To 日期]，
wh.WorkingHours
来自（
< span class =code-keyword>选择 distinct [WorkingHours]
来自 [WorkingLogs] 订单 by [WorkingHours]）wh - 获取独特工作时间列表，由此我们可以过滤最短和最长工作时间
订单 按 1 ， 2 ， 3
我想我已经解决了这个问题，但我不确定这是否是有效的方法。请查看下面的代码，如果您认为可以改进，请告诉我。
声明 @ startdate datetime
set @ startdate = ' 2016-10-17 00：00：00.000' - 起始日期
声明 @ result table
（
fromdate datetime ，
todate datetime ，
totalhours decimal （ 18 ， 2 ）
）
while @ startdate < = ' 2020-12-31 00：00：00.000'
开始
- 插入当前两周的行
insert < span class =code-keyword> into @ result
select < span class =code-sdkkeyword> @ startdate ，dateadd（D， 13 ， @ startdate ），sum（wh.Hours）
来自 dbo.UserWorkingHours wh
其中 wh.WorkingDate @ startdate 和 dateadd（D， 13 ， @ startdate ）
和 wh.UserID = 120 9
- 将开始日期设为下一个两周
set @ startdate = dateadd（D， 14 ， @ startdate ）
end
- 声明最终结果表
声明 @ fresult table
（
fromdate datetime ，
todate datetime ，
totalhours 十进制（ 18 ， 2 ），
id int
）
- 声明一个变量来设置最终结果表行中的ID，这些行与前一个行具有相同的每两周小时数
声明 < span class =code-sdkkeyword> @ id int
set < span class =code-sdkkeyword> @ id = 1
- - 声明游标及相关变量
声明 @ fd datetime
声明 @ td datetime
声明 @th decimal （ 18 ， 2 ）
声明 rc cursor
选择 r.fromdate，r.todate，r.totalhours 来自 @ result r 订单 按 r.fromdate
打开 rc
获取下一步来自 rc into @ fd ， @ td ， @th
@@ FETCH_STATUS <> -1
开始
- 如果最终结果表中没有行，则插入前一个结果表中的第一个
- < span class =code-comment>它将是起始行，因为光标按日期升序排序
如果 不 存在（选择 1 来自 @ fresult ）
开始
插入 进入 @ fresult
选择 @ fd ，< span class =code-sdkkeyword> @ t d ， @th ， @ id
结束
其他
开始
- 最终结果表中有行
- 因此获取最后一行总小时数并将其与游标中当前行总小时数进行比较
if （选择 top 1 totalhours 来自 @ fresult order by fromdate desc ）<> @ th
开始
- - 小时比较失败，即当前每两周的小时数与前两周不同
- 增加ID并在最终表格中插入行
set @ id = @ id + 1
插入 进入 @ fresult
选择 @ fd ， @ td ，< span class =code-sdkkeyword> @ th ， @ id
end
else
开始
- 当前每两周的小时数与之前的每两周小时数相同
- 在最终结果表中插入包含先前ID的行
insert < span class =code-keyword> into @ fresult
select < span class =code-sdkkeyword> @ fd ， @ td ， @ th ， @ id
end
结束
获取下一步来自 rc 到 @ fd ， @ td ， @ th
end
close rc
deallocate rc
- select * from @fresult
- 选择不同的结果以及从'日期'开始的MIN'和MAX'到期'和小时
选择 不同
（选择 min（fromdate）来自 @ fresult 其中 id = fr.id） as FROMDATE，
（选择 max（todate）来自 @ fresult 其中 id = fr.id） as TODATE，
fr.totalhours
来自 @ fresult fr
上面的SQL查询给出了以下输出;
< pre lang =SQL> FROMDATE TODATE HOURS_PER_FORTNIGHT
2016-10-17 00:00:00。 000 2016-10 -30 00:00:00。 000 6 。 00
2016-10-31 00:00:00。 000 2016-12-11 00:00:00。 000 75 。 00
2016-12-12 00:00:00。 000 2016-12-25 00:00:00。 000 10 。 00
2016-12-26 0 0:00:00。 000 2020-12-20 00:00:00。 000 75 。 00
2020-12-21 00:00:00。 000 2021-01-03 00:00:00。 000 67 。 50
请告诉我如果可以改进的话。
Dear experts,
I have the following kind of structure in the database as rows;
Three columns - User ID | Date | Working hours
Data is filled in for each user (with IDs), each date (from Jan 2010 to Dec 2020) and working hours for each day.
Now, the business process is that a user can change working hours. For example, I normally work 75 hours per fortnight. But, I want to change my hours from January 2017 to March 2017 as working only 40 hours per fortnight. In that case, the system will edit hours between those dates only and the remaining hours will be left as it is up till Dec 2020.
Now I want to pull out a report on how many hours I work in the organisation. Because of the dynamic nature and the business process about changing hours any time, I want to get the output as the following;
Test user 01/01/2010 to 31/12/2016 75 hours per fortnight
Test user 01/01/2017 to 31/03/2017 40 hours per fortnight
Test user 01/04/2017 to 31/12/2020 75 hours per fortnight
Can anyone please help me with this?
Thanks.
What I have tried:
I tried using cursors for any particular employee and using forward addition to dates and comparing previous fortnight hours to see if there was a difference to break the lines as required by the output. But I am confused as it did not serve the purpose.
Please help me.
Thanks.
You can try this code:
Select (Select MIN(frmDate.Date) From WorkingLogs frmDate Where frmDate.WorkingHours = wh.WorkingHours) [From Date], (Select MAX(toDate.Date) From WorkingLogs toDate Where toDate.WorkingHours = wh.WorkingHours) [To Date], wh.WorkingHours From ( select distinct [WorkingHours] from [WorkingLogs] Order by [WorkingHours]) wh --get the lists of unique working hours, from this we can filter the min and max date of working hours Order By 1,2,3
I think I have resolved this issue but I am not sure whether it is the efficient way of doing it. Please look at the code below and let me know if you think this can be improved.
declare @startdate datetime set @startdate = '2016-10-17 00:00:00.000' --starting point date declare @result table ( fromdate datetime, todate datetime, totalhours decimal(18, 2) ) while @startdate <= '2020-12-31 00:00:00.000' begin --insert a row for the current fortnight insert into @result select @startdate, dateadd(D, 13, @startdate), sum(wh.Hours) from dbo.UserWorkingHours wh where wh.WorkingDate between @startdate and dateadd(D, 13, @startdate) and wh.UserID = 1209 --set the start date as the next fortnight set @startdate = dateadd(D, 14, @startdate) end --declare the final result table declare @fresult table ( fromdate datetime, todate datetime, totalhours decimal(18, 2), id int ) --declare a variable to set ID in the final result table rows which are having the same fortnightly hours as the previous one declare @id int set @id = 1 --declare cursor and related variables declare @fd datetime declare @td datetime declare @th decimal(18, 2) declare rc cursor for select r.fromdate, r.todate, r.totalhours from @result r order by r.fromdate open rc fetch next from rc into @fd, @td, @th while @@FETCH_STATUS <> -1 begin --if there are no rows in the final result table then insert the first from from the previous result table --it will be the starting row because the cursor is ordered by from date ascending if not exists (select 1 from @fresult) begin insert into @fresult select @fd, @td, @th, @id end else begin --there are rows in the final result table --so fetch the last row total hours and compare it with the current row total hours in the cursor if (select top 1 totalhours from @fresult order by fromdate desc) <> @th begin --the hours comparison failed i.e. current fortnightly hours are different than the previous fortnight --increase the ID and insert the row in the final table set @id = @id + 1 insert into @fresult select @fd, @td, @th, @id end else begin --current fortnightly hours are same as the previous fortnightly hours --insert the row in the final result table with the previous ID insert into @fresult select @fd, @td, @th, @id end end fetch next from rc into @fd, @td, @th end close rc deallocate rc --select * from @fresult --select the distinct result along with MIN 'from date' and MAX 'to date' and the hours select distinct (select min(fromdate) from @fresult where id = fr.id) as FROMDATE, (select max(todate) from @fresult where id = fr.id) as TODATE, fr.totalhours from @fresult fr
The above SQL query gives me the following output;
FROMDATE TODATE HOURS_PER_FORTNIGHT 2016-10-17 00:00:00.000 2016-10-30 00:00:00.000 6.00 2016-10-31 00:00:00.000 2016-12-11 00:00:00.000 75.00 2016-12-12 00:00:00.000 2016-12-25 00:00:00.000 10.00 2016-12-26 00:00:00.000 2020-12-20 00:00:00.000 75.00 2020-12-21 00:00:00.000 2021-01-03 00:00:00.000 67.50
Please let me know if this can be improved.
这篇关于如何基于日期 - 每两周一次聚合SQL数据库中的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
