Report Help: Breaking Work Logs Down Into Grouped Time Frames

Question

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!!

Nicole McGath · Answer

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 ) ;