calculating total used days per yer

[OTWarrior]

On our system we keep a record of how many days per year an employee can take for holiday, and we calculate this based on the dates given to us (using datediff).

As the new financial year is coming up, an issue has occured.

the total amount of days is a stored in a table (lets say 20)
when a holiday is booked onto the system, the datediff calculation add it's value to another field in the table (so if going away for a week, the value would be 5).
If an employee wants to have holiday after April 1st, then the value is still added to the original added total. for example, say they have used 19 of their days on 31st march, should they take holiday after April 1st for 5 days, the system says it will take them over to 24 days. What it should do is calculate the total left after this date, making 4 days.

This is unfortunately not created by me, so I am still trying to get my head around why it was done like this:banghead: . How would one of you guys go about this problem?
Would you create a second field in the table to handle the new total?

[Tommy]

Are you (ok he did it :*)) keeping track of the number of days taken per physical year or financial year? I ask cause it sounds like you are trying to do both at the same time.:yes

Seriously though I would have to use 3 fields, 1 for the total per year (physical), 1 for the total for financial year, 1 for the total left in the physical year because I would have to zero out the financial year on a new one, and also zero out the physical one on a new one. Ok so now it is starting to look like 4 columns.

This is all based on the fact that the employee and the accountants want the same things, they just have a different way of looking at it. The employee wants to know how many they have left, the accountant want to know how many they have taken and unfortunally it all is offset by 3 months. Then it all has to add up.:bug:

I'm just typing out loud here, so feel free to disagree. I don't think datdiff will cut it just because of this "problem", I also think this is a who woulda thunk it thing, something you just don't think about till it happens and after it does you never forget. :)