Suppose we have a table emp_plan which contains 4 columns "Empid" (employeeid), Planid (Projectid), Startdate(Allocation Start date) and Enddate (Allocation Enddate). Its structure is given below:-
Create table emp_plan (empid nvarchar(200),planid nvarchar(20),startdate datetime, enddate datetime)
Also below is the script to enter sample data into the table:-
insert into emp_plan(empid,planid,startdate,enddate)
select '001','planA','2015-05-15','2015-05-30'
union all
select '001','planA','2015-05-31','2015-06-14'
union all
select '001','planA','2015-06-15','2015-06-30'
union all
select '001','planA','2015-07-10','2015-07-20'
union all
select '001','planA','2015-07-21','2015-07-30'
union all
select '001','planB','2015-06-14','2015-06-30'
union all
select '001','planB','2015-07-10','2015-07-20'
union all
select '001','planB','2015-07-21','2015-07-30'
union all
select '002','planA','2015-06-14','2015-06-30'
union all
select '002','planB','2015-07-10','2015-07-20'
union all
select '002','planB','2015-07-21','2015-07-30'
So the data contain in the table is given blow:-
Now if a employee has the same project(planid) with no gap in the next allocation segment( allocation segment means from allocation startdate to allocation enddate), then we need to join the continuous allocation segment. For example if you see the data in the table, in the first 3 rows, employee having empid 001 is allocated to the Same project (PlanA) and all allocation segments are continuous (from 2015-05-15 to 2015-06-30) as shown below:-
2015-05-15 to 2015-05-30 (1st row)
2015-05-31 to 2015-06-14 (2nd row)
2015-06-15 to 2015-06-30 (3rd row)
But the 4th row contains allocation segment which is not not continuous with the above 3 allocation segments as there is a gap between the 3rd row enddate and 4th row startdate and again 4th row is in continuation with 5th row with same employeeid 001 and project id (Plan1) as shown below:-
2015-07-10 to 2015-07-20 (4th Row)
2015-07-21 to 2015-07-30 (5th Row)
So expect result set should be
Empid Planid Startdate Enddate
001 planA 2015-05-15 00:00:00.000 2015-06-30 00:00:00.000
(1st to 3rd Row)
001 planA 2015-07-10 00:00:00.000 2015-07-30 00:00:00.000 (4th to 5th Row)
001 planA 2015-05-15 00:00:00.000 2015-06-30 00:00:00.000
(1st to 3rd Row)
001 planA 2015-07-10 00:00:00.000 2015-07-30 00:00:00.000 (4th to 5th Row)
So according to the above rule, expected result from the sample data inserted into the table (as shown in the above picture), should be same as given below:-
Expected Result
Empid Planid Startdate Enddate
001 planA 2015-05-15 00:00:00.000 2015-06-30 00:00:00.000
001 planA 2015-07-10 00:00:00.000 2015-07-30 00:00:00.000
001 planB 2015-06-14 00:00:00.000 2015-06-30 00:00:00.000
001 planB 2015-07-10 00:00:00.000 2015-07-30 00:00:00.000
002 planA 2015-06-14 00:00:00.000 2015-06-30 00:00:00.000
002 planB 2015-07-10 00:00:00.000 2015-07-30 00:00:00.000
001 planA 2015-05-15 00:00:00.000 2015-06-30 00:00:00.000
001 planA 2015-07-10 00:00:00.000 2015-07-30 00:00:00.000
001 planB 2015-06-14 00:00:00.000 2015-06-30 00:00:00.000
001 planB 2015-07-10 00:00:00.000 2015-07-30 00:00:00.000
002 planA 2015-06-14 00:00:00.000 2015-06-30 00:00:00.000
002 planB 2015-07-10 00:00:00.000 2015-07-30 00:00:00.000
So what should be the query/stored procedure to get the above result?
