3 Replies Latest reply: 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 Nov 6, 2014 1:05 PM

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,

^_^

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

WolfShade Nov 7, 2014 9:46 AM (in response to WolfShade)

Anyone?
Like Show 0 Likes (0)

Actions
2. Re: Calculate total empoyee days (as of present) that have elapsed for each month in a FY

FirstSavings Nov 7, 2014 12:45 PM (in response to WolfShade)

This may be a good start. Display by Month Year the Average and Sum Dates between Start and Stop by Project.

SELECT      CAST    (

        (CAST(MONTH(Start) AS varchar(2)) + '/01/' + CAST(YEAR(Start) AS varchar(4)))
        AS DateTime
    )

    AS DatePeriod,

    AVG(DateDiff(day, Start, Stop)) AS AverageDaysToClose,

    SUM(DateDiff(day, Start, Stop)) AS SumTotalDaysToClose,

    Name

FROM     Projects

GROUP BY CAST    (
        (CAST(MONTH(Start) AS varchar(2)) + '/01/' + CAST(YEAR(Start) AS varchar(4)))
        AS DateTime
    ),

    Name

ORDER BY DatePeriod, Name
Like Show 1 Like (1)

Actions
3. Re: Calculate total empoyee days (as of present) that have elapsed for each month in a FY

WolfShade Nov 7, 2014 1:27 PM (in response to FirstSavings)

Hi, FirstSavings,

Thanks for replying! I'll give that a shot as a starting point, and let you know.

Much appreciated,

^_^
Like Show 0 Likes (0)

Actions

Go to original post

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

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

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

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

Actions

More Like This

Legend