Monday, August 3, 2015

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?

4 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. *******************************************************************
    create function [dbo].[fx_maxend](@empid nvarchar(200),@planid nvarchar(20),@startdate datetime) returns datetime as
    begin
    declare @curr_start datetime
    declare @curr_end datetime

    set @curr_end=(select z.enddate from emp_plan z where z.empid=@empid and z.planid=@planid and z.startdate=@startdate)

    if exists(select z.* from emp_plan z where z.empid=@empid and z.planid=@planid and z.startdate=@curr_end+1)
    ---there is continuity
    begin
    set @curr_start=(select z.startdate from emp_plan z where z.empid=@empid and z.planid=@planid and z.startdate=@curr_end+1)
    return dbo.fx_maxend(@empid,@planid,@curr_start)
    end

    return @curr_end
    end
    *******************************************************************
    create function dbo.fx_minstart(@empid nvarchar(200),@planid nvarchar(20),@endate datetime) returns datetime as
    begin
    declare @curr_start datetime
    declare @curr_end datetime

    set @curr_start=(select z.startdate from emp_plan z where z.empid=@empid and z.planid=@planid and z.enddate=@endate)
    if exists(select z.* from emp_plan z where z.empid=@empid and z.planid=@planid and z.enddate=@curr_start-1)
    ---there is continuity
    begin
    set @curr_end=(select z.enddate from emp_plan z where z.empid=@empid and z.planid=@planid and z.enddate=@curr_start-1)
    return dbo.fx_minstart(@empid,@planid,@curr_end)
    end

    return @curr_start
    end
    *******************************************************************
    select
    a.empid
    ,a.planid
    ,dbo.fx_minstart(a.empid,a.planid,dbo.fx_maxend(a.empid,a.planid,a.startdate)) as min_enddate
    ,dbo.fx_maxend(a.empid,a.planid,a.startdate) as max_enddate
    from
    emp_plan a
    group by
    a.empid
    ,a.planid
    ,dbo.fx_minstart(a.empid,a.planid,dbo.fx_maxend(a.empid,a.planid,a.startdate))
    ,dbo.fx_maxend(a.empid,a.planid,a.startdate)
    order by
    1,2,3,4

    ReplyDelete
    Replies
    1. Hi Hernan Perez Andrade,
      Thanks for your correct solution.. :-)

      Delete
  3. My Solution:-

    IF OBJECT_ID('tempdb..#empplan') IS NOT NULL
    drop table #empplan

    Create table #empplan (id int identity(1,1),empid nvarchar(200),planid nvarchar(20),startdate datetime, enddate datetime,flag int)

    insert into #empplan(empid,planid,startdate,enddate,flag)
    select empid,planid,startdate,enddate,0 from emp_plan

    ;with cte1
    as
    (
    select * from #empplan
    )


    update #empplan set flag=1 from cte1 ep2 where
    #empplan.empid=ep2.empid and #empplan.planid=ep2.planid
    and dateadd(dd,1,#empplan.enddate)=ep2.startdate and #empplan.enddate<ep2.startdate

    ;with cte2
    as
    (
    select * from #empplan where flag=0
    )

    update #empplan set flag=emp2.id from cte2 emp2 where
    #empplan.empid=emp2.empid and #empplan.planid=emp2.planid and
    #empplan.id<=emp2.id and emp2.flag=0 and #empplan.flag in (0,1)

    select empid,planid,MIN(startdate),MAX(enddate)
    from #empplan group by empid,planid,flag

    ReplyDelete