SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Question of the Month (July)

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)

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

So what should be the query/stored procedure to get the above result?

Vivek's SQL Notes

Vivek johari is currently a Analyst and have more that 5.5 yeras of experience in database. He has Master degree in Computer and also he is Microsoft certified Sql DBA (MCTS)& Microsoft certified SQl BI professional(MCTS). He is also Oracle certified profession(OCP)DBA in ORACLE 10g and ORACLE 9i.He has the experience of working in PL/SQL, T-SQL and SSIS/SSRS. His work basically involved designing and optimization of the Database.He has also published many database articles on his blog Technologies with Vivek Johari.

Comments

Leave a comment on the original post [feedproxy.google.com, opens in a new window]

Loading comments...