This is similar to some other questions here, but those use a CASE which I cannot. This is on Oracle, and I will be running the query from an excel sheet. (And by the way these do not support WITH, which makes life much harder)
这与这里的其他问题类似，但这些问题都用了我不能用的情况。这是在Oracle上，我将从excel表中运行查询。(顺便说一句，这些都不支持，这让生活更加艰难)
I have a range of dates in one big table - like 1/3/2011, 4/5/2012, 7/1/2013, 9/1/2013..... Then I have another table with hours worked by employees on certain dates. So what I need to do is get a sum of number of hours worked by each employee in each intervening time period. So the tables are like
在一个大的表格中，我有一系列的日期——比如2011年1月3日，2012年4月5日，2013年7月1日，2013年9月1日……然后我有另一张桌子，上面有员工在特定日期工作的时间。所以我需要做的是得到每个员工在每段时间内工作的时间的总和。表是这样的
Dates
1-May-2011
5-Aug-2011
4-Apr-2012
....
and another
和另一个
Employee Hours Date
Sam 4 1-Jan-2011
Sam 7 5-Jan-2011
Mary 12 7-Jan-2012
Mary 5 12-Dec-2013
......
so the result should be
所以结果应该是
Employee Hours In Date Range Till
Sam 11 1-May-2011
Sam 0 5-Aug-2011
Sam 0 4-Apr-2012
Mary 0 1-May-2011
Mary 0 5-Aug-2011
Mary 12 4-Apr-2012
....
Any pointers on how to achieve this please?
有什么建议吗?
1 个解决方案
#1
1
I'm unfamiliar with Oracle SQL and it's abilities/limitations, but since you asked for pointers, here's my take:
Join the tables (INNER JOIN) with the join rule being EmployeeHours.Date < Dates.Dates. Then GROUP BY Employee, Dates.Dates and select the grouping columns + SUM(Hours). What you'd end up with (Using your sample data) is:
我不熟悉Oracle SQL和它的能力/限制，但是，既然您要了指针，我的建议是:加入表(内部连接)，加入到EmployeeHours的连接规则。< Dates.Dates日期。然后按员工、日期分组。日期和选择分组列+和(小时)。(使用样本数据)得到的结果是:
Employee | Dates | Hours Sam | 1-May-2011 | 11 Sam | 5-Aug-2011 | 11 Sam | 4-Apr-2012 | 11 Mary | 1-May-2011 | 0 Mary | 5-Aug-2011 | 0 Mary | 4-Apr-2012 | 12
With other (more complex) data, there will be more "interesting" results, but basically each row contains total hours up to that point.
对于其他(更复杂的)数据，会有更多“有趣”的结果，但基本上每一行都包含到这一点的总小时数。
You could then use that as an input to an outer query to find MAX(Hours) for all rows where Dates < currentDates and subtract that from your result.
然后，您可以使用它作为外部查询的输入来查找日期为< currentDates的所有行的最大(小时)，并将其从结果中减去。
Again, this is not a complete answer, but it's a direction that should work.
同样，这不是一个完整的答案，但这是一个应该行得通的方向。
#1
1
I'm unfamiliar with Oracle SQL and it's abilities/limitations, but since you asked for pointers, here's my take:
Join the tables (INNER JOIN) with the join rule being EmployeeHours.Date < Dates.Dates. Then GROUP BY Employee, Dates.Dates and select the grouping columns + SUM(Hours). What you'd end up with (Using your sample data) is:
我不熟悉Oracle SQL和它的能力/限制，但是，既然您要了指针，我的建议是:加入表(内部连接)，加入到EmployeeHours的连接规则。< Dates.Dates日期。然后按员工、日期分组。日期和选择分组列+和(小时)。(使用样本数据)得到的结果是:
Employee | Dates | Hours Sam | 1-May-2011 | 11 Sam | 5-Aug-2011 | 11 Sam | 4-Apr-2012 | 11 Mary | 1-May-2011 | 0 Mary | 5-Aug-2011 | 0 Mary | 4-Apr-2012 | 12
With other (more complex) data, there will be more "interesting" results, but basically each row contains total hours up to that point.
对于其他(更复杂的)数据，会有更多“有趣”的结果，但基本上每一行都包含到这一点的总小时数。
You could then use that as an input to an outer query to find MAX(Hours) for all rows where Dates < currentDates and subtract that from your result.
然后，您可以使用它作为外部查询的输入来查找日期为< currentDates的所有行的最大(小时)，并将其从结果中减去。
Again, this is not a complete answer, but it's a direction that should work.
同样，这不是一个完整的答案，但这是一个应该行得通的方向。