Shift pattern in report

[peterb58]

I have a report for a call centre who want to cover their shift pattern.

The groups I require are 7am - 3pm, 3pm - 11pm and 11pm - 7am.
The calls are stored as date/time fields.
The problem lies in the 11pm - 7am section, both in their selection and ordering.

I am using this at the moment

select tonumber(totext(timevalue({CHistStatEx.NotificationTime}),"HH"))

case 7 to 14: "7am - 3pm"
case 15 to 22: "3pm to 11pm."
case 23 to 6: "11pm - 7am"


Using CRXI and Sybase 11

Thanks

 

[MCuthill]

peterb58:

I don't know for certain, but try 2 different cases for your 11pm to 7am group... something like:

case 1 to 6: "11pm - 7am"
case 7 to 14: "7am - 3pm"
case 15 to 22: "3pm to 11pm."
case 23 to 24: "11pm - 7am"

I haven'it worked much with "Case" specifically, but thinking it wouldn't realize 24 resets to 1.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[Charliy]

Is your data clean enough to say that if it's not Shift 1 or Shft 2 then it must mb Shift 3?

If it is all you need is something like:

if {field} in 7 to 14 then "7am to 3pm"
else
if {field} in 15 to 22 then "3pm to 11pm"
else "11pm to 7am

 

[peterb58]

It definitely is having an issue with the midnight to 6am calls.

When I look at the 11pm - 7pm group, all I see are calls from 23.00 - 23:59 and 06:00 - 07:00.
The calls from 00:00 to 05:59 are being stored in a separate group with no title.

I will look at Mike solution to see if that works.

thanks

 

[peterb58]

Mike

You solution worked, thanks.
I still don't understand why it only took the few calls detailed above and left the rest in a separate group!!

 

[MCuthill]

PeterB58,

I by no means know this as "fact", but if I were to guess -- Crystal Reports knew well enough to include Hour "6" as it was stated specifically in your Case Statement. But, as Mathematically: 25, not 0,1,2,3,4,5 follow 24, it wasn't able to include those hours after 24 but not 6.

Totally just a guess, but it kinda makes sense from a strictly logic perspective.

Glad the solution worked! It also more or less confirms the thoery above, as I implicitly stated 0 to 6, which is sequential in Decimal as well as "time" numbers.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."