Summation Report With Details- Breaking Case Counts Down Into 30 Minute Increments

Hi Nicole McGath,

This type of report is possible using Sugar's Advanced Reports feature. If you're not familiar with the feature, Advanced Reports allows you to build a report based on one or more SQL queries. You cannot generate any charts with Advanced Reports in Sugar, but the information is easily exportable if you want to build a chart in another application.

The following custom query will generate the 30-minute intervals and how many case were created in their respective interval for the month of June 2023:

SELECT FROM_UNIXTIME((UNIX_TIMESTAMP(cases.date_entered - INTERVAL 240 MINUTE) DIV (30* 60)) * (30* 60)) cases_30_min_date_entered, COUNT(cases.id) cases_count FROM cases 

WHERE (cases.date_entered >= '2023-06-01 04:00:00' AND cases.date_entered <= '2023-06-30 03:59:59'
) AND  cases.deleted=0 

GROUP BY UNIX_TIMESTAMP(cases.date_entered - INTERVAL 240 MINUTE) DIV (30* 60) 

ORDER BY cases_30_min_date_entered ASC;

A few notes about the query:

• I am based on the east coast in the US (currently EDT or GMT - 4). Sugar stores all times in the database as GMT so the query needs to accommodate for my GMT offset. In the SELECT and GROUP BY, I use '- INTERVAL 240 MINUTE' (60 minutes * 4 hour offset) and in the WHERE clause, I have explicit times added to accommodate for the 4 hour offset. If you are east of the GMT time, you would use '+ INTERVAL' and your WHERE clause would subtract hours from your desired date & time range. Unless you are in one of the few areas in the world where you do not observe daylight savings, you will need to be mindful of this query and adjust as appropriate to ensure you apply the proper offset to get the appropriate results depending on when the case was created!
• If you want your time range to be dynamic (e.g. last 30 days), change the WHERE clause for cases.date_entered so it reads like the following:
  

  WHERE (cases.date_entered BETWEEN NOW() - INTERVAL 30 DAY AND NOW()) AND  cases.deleted=0 

I hope this helps!

Chris

Hi Nicole McGath,

This type of report is possible using Sugar's Advanced Reports feature. If you're not familiar with the feature, Advanced Reports allows you to build a report based on one or more SQL queries. You cannot generate any charts with Advanced Reports in Sugar, but the information is easily exportable if you want to build a chart in another application.

The following custom query will generate the 30-minute intervals and how many case were created in their respective interval for the month of June 2023:

SELECT FROM_UNIXTIME((UNIX_TIMESTAMP(cases.date_entered - INTERVAL 240 MINUTE) DIV (30* 60)) * (30* 60)) cases_30_min_date_entered, COUNT(cases.id) cases_count FROM cases 

WHERE (cases.date_entered >= '2023-06-01 04:00:00' AND cases.date_entered <= '2023-06-30 03:59:59'
) AND  cases.deleted=0 

GROUP BY UNIX_TIMESTAMP(cases.date_entered - INTERVAL 240 MINUTE) DIV (30* 60) 

ORDER BY cases_30_min_date_entered ASC;

A few notes about the query:

• I am based on the east coast in the US (currently EDT or GMT - 4). Sugar stores all times in the database as GMT so the query needs to accommodate for my GMT offset. In the SELECT and GROUP BY, I use '- INTERVAL 240 MINUTE' (60 minutes * 4 hour offset) and in the WHERE clause, I have explicit times added to accommodate for the 4 hour offset. If you are east of the GMT time, you would use '+ INTERVAL' and your WHERE clause would subtract hours from your desired date & time range. Unless you are in one of the few areas in the world where you do not observe daylight savings, you will need to be mindful of this query and adjust as appropriate to ensure you apply the proper offset to get the appropriate results depending on when the case was created!
• If you want your time range to be dynamic (e.g. last 30 days), change the WHERE clause for cases.date_entered so it reads like the following:
  

  WHERE (cases.date_entered BETWEEN NOW() - INTERVAL 30 DAY AND NOW()) AND  cases.deleted=0 

I hope this helps!

Chris