Need Help Calculating Months Excluding Same Month

[DatabasePrincess]

SQL Server 2005 sp3

This is driving me nuts. It's probably a simple solution but I'm just not seeing it.

We have a table of employment records like this (simplified):

ID      fromDate        throughDate     type
----    ----------      -----------     ----
1       2009-08-02      2010-09-12      reg
1       2010-09-13      2010-09-25      leave
1       2010-09-26      null            reg

What I need to do is calculate the number of months where type is "reg". For example, the number of months for the first record is 13 (select datediff(mm,'2009-08-02','2010-09-12')). However, I still need to count the entire month of September for 2010. So I would add one to that result giving 14 months. (5 months for 2009 + 9 months for 2010)

I skip the next record where type is "leave".

For the next record where the throughDate is null, I use the current date. So select datediff(mm,'2010-09-26',getdate()) returns 1 month. I need to count the entire month of October which is the current month so I would add 1 to that result giving 2 months. The complete total number of months is now 16. If you add that up manually, it really should be 15.

Here's the problem. I need to make sure I don’t count the same month twice. So I need to somehow exclude counting September 2010 twice. Not every employee will have this situation.

Does this make sense? Any ideas how I can do this in one query or maybe two? Thanks!

This query returns 16 using the data above:

Select sum(datediff(mm,fromDate,isnull(throughDate,getdate()))+1)
From tblEmployment
Where type = 'reg'

 

[Rhys666]

Haveyou thought of totalling the months from the earliest from to the latest date or GETDATE() irrelevant of leave months and then simply subtracting leave months?

Rhys

"The trouble with having an open mind, of course, is that people will insist on coming along and trying to put things in it"
Terry Pratchett

 

[DatabasePrincess]

Thanks for replying! Actually, that's what I ended up doing and it worked! I also ended up using a table variable (since it has to be a function) and a CTE to make sure I don't count the same month twice. It's possible someone could take leave for one week in the middle of the month. That would give them the same month/year for separate regular employment records.