7 Replies Latest reply: Mar 12, 2013 3:13 AM by jeneesh RSS

    SQL query to display a daily timeline of tasks

    macmanxie
      Hi,

      I am in need of some SQL help and guidance to help me display a list of tasks in a timeline format, I have searched the forums and google for hours on end and cannot find an example that can help me with this challenge.

      I have a simple table with the following columns:
      job no
      engineer
      start time
      end time


      and some example data would be similar to:
      (I have used dashed lines --- to illustrate spaces as the preview appears to remove them)

      JOB_NO---ENGINEER----START_TIME-----------END_TIME
      1------------john------------16-APR-09 09:00----16-APR-09 10:00
      2------------steve-----------16-APR-09 09:00----16-APR-09 10:00
      3------------steve-----------16-APR-09 10:00----16-APR-09 12:00
      4------------steve-----------16-APR-09 13:00----16-APR-09 14:00
      5------------john------------16-APR-09 12:00-----16-APR-09 15:00
      6------------dave-----------16-APR-09 10:00-----16-APR-09 11:00

      I would like to create a report which shows a day view with hours from 08:00 to 18:00 and what jobs are allocated to what engineer on that day, which means I need to somehow show the hours of the day even if no jobs have been allocated for a specific period. The output I have in mind is:


      ENGINEER--08:00--09:00--10:00--11:00--12:00--13:00--14:00--15:00--16:00--17:00--18:00
      john--------------------11111------------------666666666666666
      steve------------------22222--3333333333-------------444444
      dave-----------------------------66666

      where the numbers indicate the Job No that has been allocated to that period

      I have seen some examples in the forum that use LIMIT clause but I can't figure out how to use it for displaying hours.

      Any advice you guys could provide would be very much appreciated.

      Best regards,

      stu
        • 1. Re: SQL query to display a daily timeline of tasks
          Frank Kulash
          Hi,

          Three easy steps:
          (1) Produce a list of times (hours between 08:00 and 18:00 in this case, done in sub-query all_periods)
          (2) Explode the data so that ther is an entry for every period in which a job is being done. (E.g. transform the single row that says john is doing task 5 from 12:00 to 15:00 into three rows, one each for 12:00, 13:00 and 14:00. Done in sub-query repeated_data.)
          (3) Pivot into one row per engineer, one column per period (done in main query)
          WITH   all_periods     AS
          (
               SELECT     TO_CHAR (LEVEL + 7, 'fm00') || ':00'     AS tm
               FROM     dual
               CONNECT BY     LEVEL <= 11
          )
          ,     repeated_data     AS
          (
               SELECT  x.job_no
               ,     x.engineer
               ,     p.tm
               FROM     table_x          x
               JOIN     all_periods     p     ON      p.tm     >= TO_CHAR (start_time, 'HH24:MI')
                                   AND      p.tm     <  TO_CHAR (end_time,     'HH24:MI')
               WHERE     TO_CHAR (start_time, 'DD-MON-YYYY')     = '16-APR-2009'
          )
          SELECT       engineer
          ,       MAX (CASE WHEN tm = '08:00' THEN job_no END)     AS "08:00"
          ,       MAX (CASE WHEN tm = '09:00' THEN job_no END)     AS "09:00"
          ,       MAX (CASE WHEN tm = '10:00' THEN job_no END)     AS "10:00"
          ,       MAX (CASE WHEN tm = '11:00' THEN job_no END)     AS "11:00"
          ,       MAX (CASE WHEN tm = '12:00' THEN job_no END)     AS "12:00"
          ,       MAX (CASE WHEN tm = '13:00' THEN job_no END)     AS "13:00"
          ,       MAX (CASE WHEN tm = '14:00' THEN job_no END)     AS "14:00"
          ,       MAX (CASE WHEN tm = '15:00' THEN job_no END)     AS "15:00"
          ,       MAX (CASE WHEN tm = '16:00' THEN job_no END)     AS "16:00"
          ,       MAX (CASE WHEN tm = '17:00' THEN job_no END)     AS "17:00"
          ,       MAX (CASE WHEN tm = '18:00' THEN job_no END)     AS "18:00"
          FROM       repeated_data
          GROUP BY  engineer;
          The output, from your sample data, is:
          ENGINEER   08:00 09:00 10:00 11:00 12:00 13:00 14:00 15:00 16:00 17:00 18:00
          ---------- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
          dave                       6
          john                 1                 5     5     5
          steve                2     3     3           4
          The results you posted had john doing task 6 in the afternoon. I assume you meant task 5.

          To post formatted results on this site, type these 6 characters
          &#123;code&#125;
          (small letters only, inside curly brackets) before and after formatted text, to preserve spacing.
          That's what I did around the code, and again aroud the output, above.
          • 2. Re: SQL query to display a daily timeline of tasks
            macmanxie
            Frank,

            Thankyou so much for your quick reply and excellent answer - this is exactly what I required. My typo of entering LIMIT rather than LEVEL also didn't manage to confuse :-)

            The query executes on the command line and in SQL Developer fine, but when I run it in Application Express SQL Commands box it prompts for a bind variable for the ||':00' in the all_periods SELECT which is strange and I can't seem to escape it. Regardless, your answer is much appreciated.

            Next time I will also use the
             syntax to enter the formatted code, the plain text help guide on the right hand side of the forum message creation box could do with that being added to it's list!
            
            Thanks again,
            
            Stu                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
            • 3. Re: SQL query to display a daily timeline of tasks
              APC
              the plain text help guide on the right hand side of the forum message creation box could do with that being added to it's list!
              You're not the first person to think that. Unfortunately the new (can we still call it new?) forum software has some severe shortcomings when it comes to customising its behaviour in certain areas (the search interface is Teh Suck!)

              Cheers, APC

              blog: http://radiofreetooting.blogspot.com
              • 4. Re: SQL query to display a daily timeline of tasks
                Frank Kulash
                Hi, Stu,
                macmanxie wrote:
                ...
                The query executes on the command line and in SQL Developer fine, but when I run it in Application Express SQL Commands box it prompts for a bind variable for the ||':00' in the all_periods SELECT which is strange and I can't seem to escape it.
                That certainly is quirky!

                Instead of
                TO_CHAR (LEVEL + 7, 'fm00') || ':00'     AS tm
                try
                TO_CHAR ( TRUNC (SYSDATE) + ( (LEVEL + 7) 
                                            / 24
                                            )
                        , 'HH24:MI' 
                        )            AS tm
                which is best anyway if you think the period might change to something that's not a multiple of one hour.

                You can always use the CHR function:
                TO_CHAR (LEVEL + 7, 'fm00') || CHR (58) || '00'     AS tm
                I would expect both alternatives to work in all three front ends. (But I would have expected ':00' to work in all three front ends, too.)
                • 5. Re: SQL query to display a daily timeline of tasks
                  macmanxie
                  Once again Frank thankyou for your help, that has sorted the issue in Apex, and my report output it looking great with this query behind it.

                  Regards,

                  Stu
                  • 6. Re: SQL query to display a daily timeline of tasks
                    996238
                    SELECT [A_NUMBER] as [NUMBER] ,     
                    Count (CASE WHEN Day_of_Week = 'Sunday'      THEN [A_NUMBER] END)     AS "Sunday" ,     
                    Count (CASE WHEN Day_of_Week = 'Monday'      THEN [A_NUMBER] END)     AS "Monday" ,     
                    Count (CASE WHEN Day_of_Week = 'Tuesday'      THEN [A_NUMBER] END)     AS "Tuesday" ,     
                    Count (CASE WHEN Day_of_Week = 'Wednesday' THEN [A_NUMBER] END)     AS "Wednesday" ,     
                    Count (CASE WHEN Day_of_Week = 'Thursday'     THEN [A_NUMBER] END)     AS "Thursday" ,     
                    Count (CASE WHEN Day_of_Week = 'Friday'     THEN [A_NUMBER] END)     AS "Friday" ,     
                    Count (CASE WHEN Day_of_Week = 'Saturday'     THEN [A_NUMBER] END)     AS "Saturday" ,     
                    Count (CASE WHEN Day_of_Week in ('Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday') THEN [A_NUMBER] END)     AS "Total"
                    FROM     [table1]
                    GROUP BY [A_NUMBER]


                    Hello sir please help me this query in 99% accurate but if [A_NUMBER] is blank or Null is show = '0' in sunday,monday......saturday but if [A_NUMBER] is null then record are in table
                    • 7. Re: SQL query to display a daily timeline of tasks
                      jeneesh
                      Like..
                      SELECT [A_NUMBER] as [NUMBER] ,
                      sum (CASE WHEN Day_of_Week = 'Sunday' THEN 1 else 0 END) AS "Sunday" ,
                      sum (CASE WHEN Day_of_Week = 'Monday' THEN 1 else 0 END) AS "Monday" ,
                      sum (CASE WHEN Day_of_Week = 'Tuesday' THEN 1 else 0 END) AS "Tuesday" ,
                      sum (CASE WHEN Day_of_Week = 'Wednesday' THEN 1 else 0 END) AS "Wednesday" ,
                      sum (CASE WHEN Day_of_Week = 'Thursday' THEN 1 else 0 END) AS "Thursday" ,
                      sum (CASE WHEN Day_of_Week = 'Friday' THEN 1 else 0 END) AS "Friday" ,
                      sum (CASE WHEN Day_of_Week = 'Saturday' THEN 1 else 0 END) AS "Saturday" ,
                      sum (CASE WHEN Day_of_Week in ('Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday') THEN 1 else 0 END) AS "Total"
                      FROM [table1]
                      GROUP BY [A_NUMBER]