Dynamic calculations in a materialized view

Question

I have a problem I created a materialized view which works perfectly. But now I need to perform some calculations with the data in my view . The view contains the working hours of employees who worked in different projects. Every project has a fixed amount of time (time_available) in which the employee has to finish the project. I aggregated the working hours to month for better presentation. What I want to accomplish here is a "simple" subtraction of "fixed amount for a project" minus "working hours" for the employee. The problem here is that some project have duration of more that just one month. Naturally all my values are in one tuple for every month. So when I have 3 month of working hours for a project my view looks like this:

MV_Working_Hours:

Project --- Time_Available --- DATE --- Employee --- Working Days
Project A --- 50 Days --- 2011-05 --- Mr. A --- 15 Days
Project A --- 50 Days --- 2011-06 --- Mr. A --- 16 Days
Project A --- 50 Days --- 2011-07 --- Mr. A --- 16 Days

What I want to do is to calculate the remaining days like this :

Project --- Time_Available --- DATE --- Employee --- Working d in Month ---remaining days
Project A --- 50 Days --- 2011-05 --- Mr. A --- 15 Days --- 35 Days
Project A --- 50 Days --- 2011-06 --- Mr. A --- 16 Days --- 19 Days <--- I get here 34 which is for my need wrong !!!
Project A --- 50 Days --- 2011-07 --- Mr. A --- 16 Days --- 3 Days

Is there a way to realize this with "just" sql or do I have to use pl/sql in the OWB? I use the OWB version 11gR2

thx

Answer 1

You should be able to use analytic functions to generate the running total. Something like

SELECT project,
       time_available,
       date_column,
       employee,
       working_days_in_month,
       time_available - 
         sum(working_days) over (partition by project 
                                     order by date) remaining_days
  FROM mv_working_hours

Answer 2

You should be able to do this with analytic SQL functions.

11gR2 http://download.oracle.com/docs/cd/E14072_01/server.112/e10810/analysis.htm

SUM( "Working Days" )
  OVER (PARTITION BY "Project", "Employee"
  ORDER BY "DATE" 
  ROWS UNBOUNDED PRECEDING)

You should be able to subtract that from "Time_Available" (you'll have a potential for returning negative values, replacing those with zeros can be handled in SQL as well)

---

BTW, I can't help but like your chosen title for the result... "reaming days"

Depending on the project, Mr. A might feel that column title is appropriate and justified.