[RESOLVED] Group by Shift and Not Date

Good morning all,

I have Google fooed until I'm sick to my stomach and I am hoping someone here can help.

I am researching data related to a particular type of incident for my fire department.

The data I am pulling includes a date/time field and I have group #1 which groups the data by month and then group #2 which is by day (daily).

What I am hoping for, is to group data by 24 hour periods 07:00:00 - 06:59:59.

I am using CR 2008 and I would have bet that there was an option for grouping by shift (hour span) but if there is, I missed it.

Please help if you can.

Edited by KCowden - 23 Aug 2016 at 9:41am

You can group by day, but it appears your 'day' would not match the regular 24 hour day.  I am trying to think how you could make formula to do this. If I come up with something, I will let you know.

kevlray,

Thank you. It's a head scratcher and I'm glad to know it's not some simple thing I missed as a noob.

maybe adding time using DATEADD() to your original field and grouping on that result but displaying the original field?

DBlank,

Thank you for your response. I am hoping you could explain further. I'm okay with SQL and some web languages but a complete noob when it comes to CR or VB syntax.

Would I use the DATEADD function in a selection formula? How would I phrase it to include the next day's entries before a set time (0700)?

It is a date/time field {sytem.response_date}, E.G. yyyy-mm-dd hh:mm:ss.

I am grateful for your response but if you can suffer a little hand holding here, I'd be obliged.

Edited by KCowden - 10 Aug 2016 at 5:37am

If I understood you correctly you wanted to group by a 'day' but that the 'day' was really 7 am to 6:59:59 am the following day.
If you use a formula field on that datetime field you can 'shift the times back' by 7 hours essentially making your values utilize a regular 'day'. If you group on this result you can group into the 'correct day' but still display (and use) the original values.

example:
DATEADD("h",-7,{table.datetimefield})

Okay! Excellent solution. THANK YOU!

A little detail. I thought I needed to add the field to the report but have learned I can group on it without including the field in the report.

There is an anomaly though not important. A few of the date/time incidents are outset to the bottom of that shift's details. But it's not a big deal, just weird.