Dear community,
Let me present this as clear as I possibly can.
I want to group records that have the same device ID and with any pair of Begindates that fall within a range of 8 businesshours.
I have a report with the following fields that are relevant to my problem (or at least I think they are :):
Record Device BegindateBusinesshrs Workhours
A 100 9-9-2010 14:15:18 3
B 100 10-9-2010 10:30:30 5
C 100 15-9-2010 13:45:43 4
D 100 15-10-2010 16:40:00 12
E 100 18-10-2010 16:30 1
F 100 19-10-2010 16:00 0,5
BegindatesBussinesshrs is a formula that converts a given date to business-hours between 8:30 and 17:00 (9-9-2010 19:00 gets converted to 10-9-2010 8:30 for example). Holidays are also accounted for. I also have a formula called Workhours, that calculates the passed workinghours between 2 TimeValues of dates. Here I use it to calculate the passed workhours between BegindateBusinesshrs and EnddateBusinesshrs (the last not showed here). These slightly adjusted formulas come from the excellent site of Ken Hamady. http://www.kenhamady.com/
Before I try to make clear what result I want, I'll have to confess that I don't know if I can use a Group formula, have to use subreports, SQL queries or something else. I am not very experienced with Crystal, which makes it hard to google a complex problem like this (at least to me it is).
I want to get the passed workhours
between the Begindates of each Record with the same Device number. Then I want to group the Records where this number is =< 8. If the number of workhours between Record D and E =< 8 and the number of workhours between Record E and F =< 8, I want Record D, E and F grouped together. Finally I want to show the sum of the total number of Workhours
for each seperate record in the group.
On to my perceived result (note that there is a weekend between Record D and E, so the workinghours between them are < 8. I hope the formatting is possible, maybe I'll have to use another way.
GroupDevice100#1:
RecordsinRange BegindateRecord WorkhoursRecord Workhourstotal
A 9-9-2010 14:15:18 3
B 10-9-2010 10:30:30 5
8
GroupDevice100#2:
RecordsinRange BegindateRecord WorkhoursRecord Workhourstotal
D 15-10-2010 16:40:00 12
E 18-10-2010 16:30 1
F 19-10-2010 16:00 0,5
13,5
I really tried to make this as clear as possible and would be enormously thankful if somebody can help me.
IP Logged
