Cumulation from one year to another

[Denjam]

i'm doing this leave tracker program for my final project but i'm having a bit of problem.
the problem is that i want to cumulate the vacation days not taken with the present one for the new year. what i'm saying that if 10 days are alloted each for vacation and the person didn't took any or take only 5 then the remainder must be added to the new ones. i'm doing in vb.net 2008. is there anyway to achieve that. plz help me out.

[ramesh]

Try using [start date] and [end date] parameters into your query. This will limit your records between those dates.

[Denjam]

i don't think that would work the employees don't have a end date. what i want to do is to check the now date against the start date and check how many days they have remaining.

[ramesh]

In such a case, [end date] would be Now() or just put in today's date. Even if you put a future date the records would only be of what has already ocurred.

[Chris]

If I'm understanding the problem correctly, it sounds like you have to use a pro-rated calculation of days entitled for the current year and then add the brought-forward days from the previous year. Correct?

[digimon]

If the database designed bad from start, u might have to go pull up some tables.

If the 10 day thing is standard

what i would do is have a table for vacation and then in that table are rows with ID_numbers repeated down, and the date of the day in the other column. Then u could find out how many vacation days were taken by

select count(vac_date) where id_number = @id_num and vac_date> dateadd(year,@max_years,getdate())

This will count all days taken within a @max_years period

i used @max_years to set a cut off point, as normally organizations say u cant carry all years forward, only the last 3 years or so.