Group by question

[back2tek]

Hi All,
I am using CR9 on SQL server. I need to group by a datetime field in a table. The challenge is I need to group on this field from 6:00 am to 6:00 am next day. Any thoughts how this grouping can be achieved.

Thanks
Back2tek

 

[lbass]

Which day does the shift "belong" to? You have to decide that the range is either 6:00 AM to 5:59 AM. or 6:01 AM to 6:00 AM the next day so that there is no overlap. Assuming you choose the former, you can adjust the date so that the entire shift is attached to one of the two days by using dateadd():

dateadd("h",-6,{table.datetime})

Or:

dateadd("h",18,{table.datetime})

Insert a group on this formula and then display the actual date field in the report.

-LB