0 Replies Latest reply: Apr 26, 2016 11:28 AM by Steve Solun RSS

    Analysis of Scheduled Tasks Help

    Steve Solun

      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