Trying to group days for 7am to 7pm 1

[AZdesertdog]

Using CR8.5 with MSSQL2k

I have a report that counts procedures based on two 12 hours shifts: 7am-7pm, 7pm-7am. I have the formulas that count the procedures in a particular shift;

@Shift 1:

if time ({scheduleddate}) in time(7,0,0) to time(18,59,59) then 1 else 0

@Shift 2:

if (time ({scheduleddate}) in time(19,0,0) to time(23,59,59) or 
time (scheduleddate}+1) in time (0,0,0) to time(6,59,59) )
then 1 else 0

Current output:
<GH1>shift 1 # shift 2 #
<GF1> 50 30

Wanted: to group them by the 12 hour shift
<GH1> Date Jan 1
<GH2> shift 1 # shift 2 #
<GF2> 30 20 <- these may actually be occuring in 1/2 between midnight and 7am

<GH1> Date Jan 2
<GH2> shift 1 # Shift 2 #
<GF2> 20 10 <- these may actually be occuring in 1/3 between midnight and 7am


I want to create another grouping with a formula that treats days like the 7a-7p shift does, that is if on jan 1 any procedures performed between midnight and 0700 are counted as being part of shift 2 the day before and procedures from 7am to 7pm are counted as part of todays shift 1 and those performed 7pm to 7am tomorrow (1/2) are counted as todays shift 2. So basically, whatever the actual date is for Shift 1 (7a-7p) is used as the date for both shifts. Any help appreciated!

-dog

 

[MrBillSC]

If you want Jan 1; 7:00 p.m. to Jan 2; 7:00 a.m. to be counted for Jan 1 only, then create a date formula that checks the time of day:

// date formula:
If {table.timefield} < Time(07,00,00) then
{table.datefield} - 1 else
{table.datefield}

Group on this formula.

MrBill

 

[AZdesertdog]

That's it. Thanks!!