Exclude months between dates

[scoobyroo]

We are trying to exclude all the months a person is on leave in a calculation. Our year runs from November - October. This year we want to exclude any leaves between 11/1/2011 and 10/31/2012. Leaves can overlap years. There can be multiple leaves for one person. What would be the best way to do something like this?

The leave table is set up like this:

LeaveID EmplID LeaveTypeID LeaveBeginDate LeaveEndDate IsPaidFlag
296 1928 Other Leave 10/10/2011 1/29/2012 TRUE
345 1928 Other Leave 3/12/2012 6/11/2012 TRUE


The result should be to exclude the following months:

11/1/2011
12/1/2011
1/1/2012

3/1/2012
4/1/2012
5/1/2012
6/1/2012

 

[scoobyroo]

I should also state that we not only want to exclude the hours from the months on leave, but the calculation requires we count the number of months not on leave.

 

[JonFer]

What is the structure of the table you'd be selecting from and excluding the leave months? Can you show a few rows of data?

 

[scoobyroo]

The data looks like this:

LeaveID EmployeeID LeaveTypeID LeaveBeginDate LeaveEndDate IsPaidFlag


296 1928 Other Leave 10/10/2011 1/29/2012 TRUE
345 1928 Other Leave 3/12/2012 6/11/2012 TRUE

 

[ralphtrent]

Why in the first set of expectations are are leaving out October, but in the second send are you including March. In both examples, the leave starts in October or March. Is that just a typo?

 

[ralphtrent]

Also what is the final result supposed to be? I know what you want to not show but what do you want to show, the months a person was not on leave. so in your examples of leaving out

11/1/2011, 12/1/2011, 1/1/2012, 3/1/2012, 4/1/2012, 5/1/2012, 6/1/2012

do you want to then show

2/1/2012, 7/1/2012, 8/1/2012, 9/1/2012, 10/1/2012

??

 

[scoobyroo]

Our year goes from November to October. So, if I am trying to calculate for this year, I only look at dates between 11/1/2011 and 10/31/2012. In the example above, yes I only want to include the following months in my calculation:

2/2012
7/2012
8/2012
9/2012
10/2012

 

[ralphtrent]

OK I have something that might work for you, please take a look

// I am using a table var for testing, but this would be your physical table 
// i added an additional employee to see how it work out
declare @t1 table (id1 int, id2 int, id3 int, date1 datetime, date2 datetime, flat1 bit)
insert into @t1 values (345	,1928	,1,'3/12/2012' 	,'6/11/2012',1)
insert into @t1 values (296	,1928	,1,'10/10/2011' ,'01/29/2012',1)
insert into @t1 values (291	,1918	,1,'9/10/2011' ,'1/14/2012',1)

//I will create a cursor to populate a table will all the months i want to exclude
declare @t2 table (date1 char(7), empId int)
declare emp cursor for 
select date1, date2, id2 from @t1
open emp
declare @D1 datetime, @D2 datetime, @EMP_ID int

fetch next from emp into @D1, @D2, @EMP_ID
while @@FETCH_STATUS = 0
	begin
		declare @MonthsToAdd int, @MonthsAdded int
		
		declare @CurrentDate datetime
		select @CurrentDate = @D1
		
		select @MonthsToAdd = datediff(month, @D1, @D2) + 1 
		select @MonthsAdded = 0
		while(@MonthsAdded < @MonthsToAdd)
			begin
				insert into @t2 values (convert(char(7),@CurrentDate , 111), @EMP_ID)
				select @CurrentDate = dateadd(month, 1, @CurrentDate)
				select @MonthsAdded= @MonthsAdded+1;
			end
		fetch next from emp into @D1, @D2, @EMP_ID
	end

close emp
deallocate emp

// this table variable will hold 12 rows, one for reach month in your calendar
declare @t3 table (date1 char(7))
insert into @t3 values ('2011/11')
insert into @t3 values ('2011/12')
insert into @t3 values ('2012/01')
insert into @t3 values ('2012/02')
insert into @t3 values ('2012/03')
insert into @t3 values ('2012/04')
insert into @t3 values ('2012/05')
insert into @t3 values ('2012/06')
insert into @t3 values ('2012/07')
insert into @t3 values ('2012/08')
insert into @t3 values ('2012/09')
insert into @t3 values ('2012/10')

select t3.date1 , t2.empid
from  @t3 t3 
 left join @t2 t2 on t2.date1 not in (select date1 from @t3) 
where 
t3.date1 not in (select date1 from @t2 where empid = t2.empid)
// I need this group by other wise i return each row twice but only for employee 1918
// not sure why
 group by t3.date1, t2.empid
 order by empid

Here is my output

date empId
2012/02	1918
2012/03	1918
2012/04	1918
2012/05	1918
2012/06	1918
2012/07	1918
2012/08	1918
2012/09	1918
2012/10	1918
2012/02	1928
2012/07	1928
2012/08	1928
2012/09	1928
2012/10	1928

 

[ralphtrent]

sorry I just noticed my comment tags are C#, not SQL, darn habits, please remember to change // to -- when you review.

 

[scoobyroo]

Thanks for the quick reply. I will give this a try and let you know how it works out. Appreciate the help.

 

[JonFer]

Can you provide the structure of the OTHER table which has the EMPLID, "WorkMonth" (or date?), and Hours? Is there one record per EmplID per month already or is there a date range for their time working? Or is it just a list of EmplIds and you're assuming they're full time for the months except for the leave periods?

 

[scoobyroo]

The cursor worked like a charm. Thanks so much!

 

[ralphtrent]

Glad it worked out.