Group by range 1

[jvolden]

I am using Crystal reports 10 and would like some help with grouping.

I want to run a report on calls received during third shift (11:00PM to 7:AM). This report will be run for multiple days so I will need to group by date. The problem is it will pull calls for a persons shift from the morning and the night.

For example
person x worked from 12:00AM to 7:00 AM Monday morning. Then came back at 11:00PM and worked until 11:59 PM Monday night.

I need the grouping to be able to distinguish between calls received on these two seperate shifts for this person.

I would like idealy to have a group that is a range - currentdate, 23,00,00 to currentdate + 1,7,00,00.

Is there a way to do this? I am also open to other ideas.

I can supply what ever information you may need to figure this out.

Thank you.

 

[BlurredVision]

You could make a 'Shift' formula and group by that.

If calls received between (11:00PM to 7:AM) then '3rd Shift' else if called received between (08:00AM to 5:00PM) then 1st shift..

Just a thought.

 

[MisterMo]

Could you give a sample of the data that will be used for the report

How does the data is set on the database, how many tables are involved

Mo

 

[lbass]

I think you could manage this by adjusting the time (for the purposes of grouping) based on your definition of whether the third shift belongs to the day in which it starts or the day in which it ends.

-LB

 

[jvolden]

Thank you for the response all. Here is the database information

table.status table.statusduration table.statusdatetime
Available 8000 8/20/2005 7:00:00
*
*
Away 54000 8/20/2005 23:00:00
Available 7205 8/21/2005 01:05:00
On ACD Call 1000 8/21/2005 01:21:66

This person had a shift end at 7 in the morning and began a new shift at 10 at night. I need a way to add up the status duration for each status during each shift.

My problem is I do not know how to split these days into two seperate shifts and then group them. I need them grouped because this report will be run for multiple days at a time.

Hope this helps. I'm still stumped. Thank you.

 

[lbass]

You have to decide whether to count the shift toward the day that the shift starts or the day that it ends. Once you've made that decision, you should be able to get some helpful feedback.

-LB

 

[jvolden]

I would like to count the shift toward the day the shift starts. Thank you.

 

[lbass]

Create a formula:

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

Use this formula to group on. This should bring all shifts into the correct day group. Then you could insert a group based on time, as in:

if time({table.datetime}} in time(7,0,0) to time(14,59,59) then "1st Shift" else
if time({table.datetime}) in time(15,0,0) to time(22,59,59) then "2nd Shift" else
if time(({table.datetime}) in time(23,0,0) to time(23,59,59) or
time({table.datetime}) in time(0,0,0) to time(06,59,59) then "3rd Shift"

-LB

 

[jvolden]

Thank you! Now because I like to learn from posts, not just get answers, can you please explain why you subtract 7 and what you would have done different if I had said I wanted it to count towards the day the shift ends.

This is comming very close to working.

As it is now, it is displaying:
GH1 8/1/2005
GH2 3rd Shift
D 8/1/2005 7:00:46AM
D 8/1/2005 7:03:07AM
D 8/2/2005 12:00:00AM
D 8/2/2005 12:21:39AM

The 8/1 times should be for 11Pm to midnight.

Thank you for your help.

 

[lbass]

All it does is change the datetime to seven hours earlier, placing all times within the date the shift belongs to. If you wanted it to attach the date the shift ended, we would have added one hour to the datetime.

However, your post shows that something has been implemented incorrectly. The formula should be only used for grouping, and then use the actual datetime field in the formula for the shifts. Please post the formulas you used for each.

-LB

 

[jvolden]

The implementation was incorrect on my part, your formulas work perfectly. Thank you very much for your help.