CODEWITHSUNDEEP
sqlexceloracle-databaserange
user2689782
user2689782

Reputation: 757

SQL : Group By on range of dynamic values

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)

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

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?

Upvotes: 1

Views: 591

Answers (1)

Amit
Amit

Reputation: 46323

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:

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.

Again, this is not a complete answer, but it's a direction that should work.

Upvotes: 1

Related Questions