carry over holiday

[swarv]

All,

I have searched on the forum lots and lots but still can't find a solution to this. If you know of where there is a solution then please do let me know.

I have a holiday database here where users log on and then open a form and enter their holiday using a calendar window. It then copies those 2 dates to a waiting table. Another user comes along and approves that data and then it gets placed in a holidaytaken table and when the original user logs back on it shows the holiday and also how many days they have taken. It also works out the days taken - bank holidays etc...
Our holiday year runs from the 1st jan to end of year. Now if a user wants say from the 26th Dec to the 5th Jan off I want it to take the 26th - 31st Dec from first years holiday entitledment and the 1st - 5th Jan from the second years entitlement.
Also if the user has 5 days left entitlement from year 1 I want it to carry that over to year 2.
Is any of this possible?

Thanks.

Martin

 

[DCrake]

I assume that in your holiday entitlement table you will have the following

EmployeeId
Holiday Start
Holiday End
Duration

The duration would have been caculated at the point of entry, by extracting the actual working days between the two dates. Taking into account bank holidays and weekends.

Your employee table will hold that maximum number of days entitlement per year. Another field to hold the carry over days form the previous year. And one to hold the entitlement left.

If, as in your example, the range crosses over a financial year you would need to calculate the duration between the start date and the cut off date

Year 1
Full Entitlement = 25 days
Entitlement left = 5 days
Entitlement carryover = 0

26 Dec (Fri) = 1
27 Dec (Sat) = 0
28 Dec (Sun) = 0
29 Dec (Mon) = 1
30 Dec (Tue) = 1
31 Dec (Wed ) = 1

Sub Total = 5

Entitlement - sub total (5-7) = 2

Now update the Entitlement left with 0

Year 2
1 Jan (Thu) Bank Holday and cuttoff date = 0

2 Jan (Fri) = 1
3 Jan (Sat) = 0
4 Jan (Sun) = 0
5 Jan (Mon) = 1

Sub total 2 days

Full Entitlement = 25 days
Entitlement left = 23 days (full entitlement - carryover)
Entitlement carryover = 0

The above would work on loop reading in each date in the range and checking for the necessary flags. You would have a running sum within the loop to calculate the sub totals.

This is all conjecture and not proven. But in theory this is how I would have tackled it.

David