Analysis of Scheduled Tasks Help

Tue, 26 Apr 2016 14:41:13 GMT

Hi,

I have an excel with 2 tabs:

1) Scheduled Task definitions data

2) Scheduled Tasks run time info (14 Months) ~ 248K rows.

My goal is to make full analysis.

Please help me with the following:

I have execution date field (EXEC_TIME) of type time stamp (01-02-2015 10:34:53 AM) - I want to split it to be generic days and months:

Give me all Mondays, Sundays etc.., Give me all January, February etc..

So for example I want all the Mondays with their run times (Generic day): Mon 1232:44:22 (HHHH:MM:SS).

Or Monthly run time: January: 10000:11:33.

NOTE: The main problem I face is that I have a lot of Mondays, Tuesdays etc...

I need a solution like floor in order to get rid of repetitions. Day names and months should be distinct.

Finally I would like to show the run time for all Sundays, Mondays etc...

Same with months.

Here is my script so far:

[Scheduled Task Run Time Data]:

LOAD

TASK_ID,

EXEC_TIME,

END_TIME,

Date(floor(EXEC_TIME)) as EXEC_DATE,

Date(floor(EXEC_TIME), 'WWW') as EXEC_DAY,

Date(floor(EXEC_TIME), 'MMM') as EXEC_MON,

END_TIME - EXEC_TIME as RUN_TIME,

COMMENTS as RUN_TIME_COMMENTS,

TASK_EXEC_USER,

PARENT_ID,

TRIGGER_NAME,

EXEC_STATUS

FROM [lib://Materials for Work/ScheduledTasksAnalysisStage3.xlsx]

(ooxml, embedded labels, table is ALLRunTime);

Please help me what should I right in the script.

Thanks in advance!

Steve

topic Analysis of Scheduled Tasks Help in App Development