// 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
