3 Replies Latest reply on Nov 7, 2014 1:27 PM by WolfShade

    Calculate total empoyee days (as of present) that have elapsed for each month in a FY

    WolfShade Level 4

      Hello, everyone,

      I'm running CF server connecting to an Oracle (11g)  db.

      I have a table that keeps track of projects (past, present, future) and need a way to sum the days for each project, for each month, that have happened.

      For example.

      ID   - Name          -   Start -   Stop  - Employees
      001 Project A      01-Oct-14 31-Dec-14       8
      002 Project B      06-Feb-15 21-Feb-15       9
      003 Project C      30-Oct-14 29-Nov-14       3
      004 Project D      26-Dec-14 16-Apr-15      21

       

      I'm trying to create an overview where the display will have the months of a Fiscal Year go across the page, indicating the number of employee days for each project that have elapsed. (NOT counting the current day).  New requirement: To make matters even more complex, there is a related table for the employees showing what dates they actually started and stopped (not every employee starts on day 1 and ends on the last day.. some start a day or two after the project start, etc.)

       

      I'm not wrapping my head around this, for some reason. Mental block. Any ideas?

       

      Someone in another forum suggested a PIVOT.  That's above my paygrade (I have never used PIVOT, don't understand how it works, etc.)

       

      V/r,

       

      ^_^