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]:
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,
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!