date expression over my head

[Guest]

I'm stumped. I need to write an expression that, working off a table of
dates, number of days taken, and type of leave, calculates the number of sick
days someone has left in the current fiscal year. The table says, for
example, that the employee took "6" "vacation" days, beginning on "8/31/02"
and later subtracts that from the number earned. Our fiscal year is 11/01 to
10/31, so I need it to say: add up the "number" of "sick" days taken since
the beginning of the current fiscal year and subtract that from 10. The goal
is to ignore anything that falls outside this date range and account for
records that bridge two fiscal years. If this is not the best newsgroup to be
posting this question, I'd appreciate a heads-up as to where to turn for
help. Thanks. Phil Kay

 

[[MVP] S.Clark]

SELECT EmpID, Count(Date) as SickDays GROUP BY EmpID WHERE SomeCode = "SICK"
Where WorkDate Between [Enter Start Date] and [Enter End Date]

This type of query will get you the number of Sick Days per Employee. From
there, you can work on subtracting the SickDays value from 10.