Networkdays Function

[#1]

Hey guys,

So I am trying to create a field in the script that is the # of days a task was open. Please review the following:

Tasks:

Load

%Task_ID,

Task_Start_Date,

Task_Compl_Date

From [Data Source]

;

Left Join

Load

%Task_ID,

NETWORKDAYS(Task_Start_Date, Task_Compl_Date) as DaysOpen

Resident Tasks

;

What I get is a number for each unique set of dates but when a date repeats, like 2 different tasks that were completed on the same date, I only get a value for one and null for the other. This is because it's only doing the function for unique sets of dates. Example:

Task_Start_Date    Task_Compl_Date     DaysOpen

01-02-2012             01-03-2012               2

12-23-2011             01-03-2012               NULL

Im sure it's simple but I'm not sure how to correct this.

Thanks for any and all help.

[m_woolf]

Include the task ID in your chart.

[#3]

I should have included %Task_ID in my example, sorry. It doesn't help though, becuase the script is simply not creating records when there is a repeated date.

[m_woolf]

Try without the join load:

Tasks:

Load

%Task_ID,

Task_Start_Date,

Task_Compl_Date,

NETWORKDAYS(Task_Start_Date, Task_Compl_Date) as DaysOpen

From [Data Source]

;

[#5]

I am only doing the join load because the date I am using (Copletion date) is actually being created in the intial load. So what I did is Instead of only doing %Task_ID and the Networkdays function I did this:

Tasks2:

Load *,

NETWORKDAYS(Task_Start_Date, Task_Compl_Date) as DaysOpen

Resident Tasks

;

Drop Table Tasks; //Dropped the original

and it seems to be working hoever the averages are not correct which I believe may be a seperate problem altogether.