Time Ranges and extrapolating values

[JamesFlowers]

I have a timesheet looking like this

Start End Hours
Saturday 22:00 06:00 8.00
Sunday 01:00 08:00 7:00
Monday 10:00 17:30 9.00

I am trying to extrapolte from these ranges if a time epriod enters a specfic time range eg 00:00 to 04:00 and then 04:00 to 06:00 and tell me how many hrs is in the range

so for above
0-4 4-6
Saturday 4 2
sunday 3 2
Monday 0 0

the totals are the easy part I just cant see how to extrapolte the portions of the time ranges in CR8.5

TIA

James

 

[synapsevampire]

Since you're not saving these as datetime fields, thsi is a bit tricky (and the database design is suspect).

And what your timesheet looks like means nothing, provide what your TABLE and DATA look like, along with data types.

Anyway, because the design is lame, we'll need a coupla dumb formulas:

@Start
whileprintingrecords;
Timevar Start := ctime({table.start});
If hour(start) > 6 then
Start:=ctime("00:00:00");

@End
whileprintingrecords;
Timevar End := ctime({table.End});
If hour(End) > 6 then
End:=ctime("06:00:00");

This will always reset your times to the times you're interested in and they are now valid time types so you can use them in more conventional approaches to getting the times.

-k

 

[lbass]

Using SV's variables, but assuming these are already time fields, try something like:

//{@hours0to4}:
whileprintingrecords;
timevar start := {table.start};
timevar end := {table.end};
if hour(start) > 6 then start:=time(0,0,0);
if hour(end) > 6 then end:=time(6,0,0);

if start >= time(0,0,0) and
end <= time(4,0,0) then
end - start else

if start in time(0,0,0) to time(4,0,0) and
end > time(4,0,0) then
time(4,0,0) - start else

if start > time(4,0,0) then 0

//{@hours4to6}:
whileprintingrecords;
timevar start := {table.start};
timevar end := {table.end};
if hour(start) > 6 then start:=time(0,0,0);
if hour(end) > 6 then end:=time(6,0,0);

if start < time(4,0,0) then
0 else
if start > time(4,0,0) and
end <= time(6,0,0) then
end - start

-LB