boldCounting Hours in a Date Range

[kettie]

I am attempting to retrieve the number of hours leave taken within a given period.

The problem I'm having is that when I specify a date range, the report brings back only those records where the leave commences within the date range and not any leave that started prior to the commencing date in the range.

The 3 relevant fields are LVE_STARTD, LVE_ENDD & LVE_HRS_TKN (this only counts the total leave from the start to end date)

For example, if I'm reporting on all leave taken during the period 1/11 to 30/11 and a person was on leave from 28/10 to 3/11, none of their leave will be reported. However, what I would want to see would be the hours of leave on 1/11, 2/11 and 3/11 only.

I'm not sure that this can be done, but I would appreciate any advice
Thanks

 

[jimmarkham]

Assuming you are getting records from a database then selecting them should be possible.

To select all records where the leave overlaps the period startdate to enddate the conditions are

lve_startd < enddate and lve_endd > startdate

(i.e. the leave must start before the end of the period in question and end after the start of the period in question)

I don't know how you will work out the proportion of hours in the leave period that overlap the required date range though - sorry.

Jim

 

[kettie]

Thanks jimmarkham

Your suggestion worked. I too don't know how I'm going to work out the proportion of hours in the leave period, but at least I can now isolate the leave in the period!
from the land down under