Web Analytics Made Easy - Statcounter

Question of the Month

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:-4
 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?


Discover more from Technology with Vivek Johari

Subscribe to get the latest posts sent to your email.

By vivekjohari

I am currently working as a Senior Database Professional and have around 18 years of experience in database. Degree:- Master Degree in Computer(MCA) Certification course in Data Science & Machine Learning from Indian Institute of Technology (IIT), Delhi Work experience:- Designing of the database. Database Optimization. Writing Complex Stored Procedures,Functions,Triggers etc. Designing and developing SSIS & DTS packages. Designing SQL Reports using SSRS. Database Server Maintenance. Certification:- MCTS: DA-100: Analysing Data with Microsoft Power BI MCTS: DP-300: Administering Relational Databases on Microsoft Azure Microsoft certified Sql DBA in Sql server 2008 (MCTS). Microsoft certified BI professional in Sql server 2008 (MCTS). Oracle certified profession DBA in ORACLE 10g (OCP) certified profession DBA in ORACLE 9i (OCP) My other publication Technical Blog:- Technologies with Vivek Johari Guest Author and Blogger at sqlservercentral.com

23 thought on “Question of the Month (July)”
  1. *******************************************************************
    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

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

  3. Of those who got two doses of the GSK vaccine, only 13 developed active tuberculosis during three years of follow-up, according to the new study published in The New England Journal of Medicine. By contrast, 26 of those who got a placebo progressed to active tuberculosis.
    https://www.ccss77.com

Leave a Reply

Discover more from Technology with Vivek Johari

Subscribe now to keep reading and get access to the full archive.

Continue reading