Find out Total Time Taken by each Project

[MK_QSL]

1) NEED SOLUTION IN SCRIPT ONLY

2) TOTAL TIME Taken by Each Sub_Project also needed in Minutes

I need to find out Total Time Taken by each project..... i.e. X and Y

Project	Sub_Project	ProjectID	StartTime	EndTime
X	X.A	1001	01/07/2014 10:00:00	02/07/2014 00:00:00
X	X.A	1002	02/07/2014 00:00:00	03/07/2014 00:00:00
X	X.B	1003	02/07/2014 11:00:00	02/07/2014 14:00:00
X	X.A	1004	03/07/2014 00:00:00	03/07/2014 22:00:00
X	X.C	1005	03/07/2014 21:00:00	03/07/2014 23:30:00
Y	Y.B	1006	05/07/2014 14:00:00	06/07/2014 00:00:00
Y	Y.A	1007	05/07/2014 22:00:00	06/07/2014 00:00:00
Y	Y.A	1008	06/07/2014 00:00:00	07/07/2014 00:00:00
Y	Y.B	1009	06/07/2014 00:00:00	07/07/2014 00:00:00
Y	Y.A	1010	07/07/2014 00:00:00	08/07/2014 00:00:00
Y	Y.B	1011	07/07/2014 00:00:00	08/07/2014 00:00:00
Y	Y.A	1012	08/07/2014 00:00:00	09/07/2014 00:00:00
Y	Y.B	1013	08/07/2014 00:00:00	08/07/2014 16:00:00
Y	Y.A	1014	09/07/2014 00:00:00	10/07/2014 00:00:00
Y	Y.C	1015	09/07/2014 14:00:00	09/07/2014 16:00:00
Y	Y.A	1016	10/07/2014 00:00:00	10/07/2014 10:00:00

Also, to be noted, that any Sub_Projects falling in same time should not be calculated twice.

i.e.

Time taken by 1003 is already covered in 1002 so would not be counted...

Time taken by 1005 is actually 150 min but due to 60 mins already covered in 1004, will be counted as 90 min

Time taken by 1008 is completely matching with 1009, so only one of them would be counted , same with 1010 and 1011

1013 is covered in 1012, so will give 0 time

and so on...

[maxgro]

my attempt (hope to understand the question)

the idea is to generate every minute with iterno and then count distinct

[martynlloyd]

Hi,

SubProject:

LOAD
Project,
Sub_Project,
ProjectID,
Interval(EndTime-StartTime, 'mm') as Duration_SP
from ... ;

Project:
LOAD
Project,
Sum(Duration_SP) as Duration
Resident SubProject
Group By Project;

ML.

[martynlloyd]

Oops. Sorry, should be Interval#

And, of course, you will need to convert the duration back to a time to display (1=24hrs, 0.25=6hrs, etc), using Interval or Time, and your chosen format.

[PradeepReddy]

Hi Manish,

Please find the attached application, it might be helpful.

Regards,

Pradeep

[rbecher]

Hi Manish,

if you do not have gaps in between the time span the project duration would be:

LOAD
Project, 
timestamp(Min(StartTime)) as StartTimePrj,
timestamp(Max(EndTime)) as EndTimePrj,
Interval(Max(EndTime) - Min(StartTime)) as Duration
Resident Data
Group By Project;

- Ralf

[jagan]

Hi Manish,

Try like this

ProjectDetails:

LOAD

*

FROM DataSource;

ProjectDuration:

LOAD 

Project,  

Interval(Max(EndTime) - Min(StartTime), 'D') as DaysTaken

Resident ProjectDetails

Group By Project;

Regards,

Jagan.

[martynlloyd]

Hi Manish,

I believe this will solve your problem?

SubProject

LOAD Project,
     Sub_Project,
     ProjectID,
     StartTime,
     EndTime,
Interval(EndTime-StartTime, 'mm') as Duration_SP
FROM
[..\Community.xlsx]
(ooxml, embedded labels, table is Sheet1);

Project:
LOAD
Project,
Min(StartTime),
Max(EndTime),
Interval(Max(EndTime)-Min(StartTime))
Resident SubProject
Group By Project;

Regards..

[MK_QSL]

Thanks for your reply.. .I am testing all replies...