tsql - T-SQL absence by month from start date end date -

I have an interesting question and am trying to find the best way to do this. Actually I have an absence table in our employee database that records the employee ID and then for a non-attendance, a start date and expiry date. The last date is taping if not yet registered (not back) I can not change the design

They would like a report on the number of absences (12 month trend) in the month. Closing in the month change with the staff can be difficult to calculate clearly.

For example, employees from 25/11/08 to 05/12/08 (DD / MM / Yani) want to go November in November in November and in December in December.

Currently I am thinking of calculating the number of need to separate the beginning and end date in each record for each day of absence, to specify it by that month. Then the data group for reporting Without the end date of those people, I think they are null current dates because they are still absent.

What would be the best way to do this?

Any better ways?

EDIT: This is currently the SQL 2000 server. I am awaiting an upgrade soon.

The last date designed for data collection but not for reporting.

I found the "Fastest Performance" solution and found that it had to create a second table with monthly prices. I populated it with months from January 2000 to January 2070. I hope this will be enough or in 2070 I get a big salary check and to update it ...

  DECLARE TABLE months (initially DATETIME) - all months starting Populate with dates which can be ever needed - and I will suggest months of selecting the start of serial / primary keying for months. DATEDIFF (DAY, months.start, data.end) ELSE DATEDIFF (DAY, data.startup, DATEADD (month, 1, month, startup) since start, data. Id, sum (case when data .tarting & lt; months) ) END) Include data in months on data in the coming days. Start and lieutenant; DATEADD (months, 1, months beginning) and data.ed & gt; Until months Month by group Start, data.id  

This can be very slow for a number of reasons, I will figure out another question to show why and how to include optimization.

Edit:

Another answer related to overlapping date ranges and how it is involved in speed ...