Details about this thread
  • State Suggested Answer
  • Replies 1 reply
  • Answers 1 answer
  • Subscribers 310 subscribers
  • Views 618 views
  • Users 0 members are here
Related content from around SugarClub

Report Help: Breaking Work Logs Down Into Grouped Time Frames

Nicole McGath

Hello All!

I am trying to create a report that will show me a break down of our work logs by time slots per day.  

For example, I would like to see how many case work logs are created between 8-10am, 10-12am, and so on.

All I have been able to do so far is show me the exact times that each one is entered. 

I have tried every way that I can think of and still cannot get it.  Please help!!

Parents
  • 0

    Hi Nicole,

    There are a couple of ways to achieve this:

    1. Advanced reports (cons - No chart and requires Sugar Enterprise)

    2. Code change - Create a new field and in a logic hook, populate your time range - which can then be used to group in the Reports.

    For advanced reports - You can write your own custom grouping by hour, something like:

    select case
               when hour(date_entered) between 0 and 2 then 'Midnight - 2AM'
               when hour(date_entered) between 2 and 4 then '2 - 4 AM'
               when hour(date_entered) between 4 and 6 then '4 - 6 AM'
               when hour(date_entered) between 4 and 6 then '6 - 8 AM'
               when hour(date_entered) between 8 and 10 then '8 - 10 AM'
               when hour(date_entered) between 10 and 12 then '10am -12 Noon'
               when hour(date_entered) between 12 and 14 then '12 - 2 PM'
               when hour(date_entered) between 14 and 16 then '2 - 4 PM'
               when hour(date_entered) between 16 and 18 then '4 - 6 PM'
               when hour(date_entered) between 18 and 20 then '6 - 8 PM'
               when hour(date_entered) between 20 and 22 then '8 - 10 PM'
               when hour(date_entered) between 22 and 24 then '10pm - Midnight'
               end  time_range,
           count(*) count
    from cases
    group by 1
    order by hour(date_entered)
    ;
Reply
  • 0

    Hi Nicole,

    There are a couple of ways to achieve this:

    1. Advanced reports (cons - No chart and requires Sugar Enterprise)

    2. Code change - Create a new field and in a logic hook, populate your time range - which can then be used to group in the Reports.

    For advanced reports - You can write your own custom grouping by hour, something like:

    select case
               when hour(date_entered) between 0 and 2 then 'Midnight - 2AM'
               when hour(date_entered) between 2 and 4 then '2 - 4 AM'
               when hour(date_entered) between 4 and 6 then '4 - 6 AM'
               when hour(date_entered) between 4 and 6 then '6 - 8 AM'
               when hour(date_entered) between 8 and 10 then '8 - 10 AM'
               when hour(date_entered) between 10 and 12 then '10am -12 Noon'
               when hour(date_entered) between 12 and 14 then '12 - 2 PM'
               when hour(date_entered) between 14 and 16 then '2 - 4 PM'
               when hour(date_entered) between 16 and 18 then '4 - 6 PM'
               when hour(date_entered) between 18 and 20 then '6 - 8 PM'
               when hour(date_entered) between 20 and 22 then '8 - 10 PM'
               when hour(date_entered) between 22 and 24 then '10pm - Midnight'
               end  time_range,
           count(*) count
    from cases
    group by 1
    order by hour(date_entered)
    ;
Children
No Data