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