Question of the Month (July)

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?

About vivekjohari

Database Consultant with more than 11.5 years of experience in database designing & programming and DBA related activities.  Had good experience on different databases like SQL Server, MySQL & Oracle, Azure SQL &  Big Data.
This entry was posted in Interview Questions and tagged , . Bookmark the permalink.

23 Responses to 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. admin says:

    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. Pingback: pair rings for couples

  4. Pingback: 온라인카지노

  5. Pingback: 카지노사이트

  6. Pingback: m.w88

  7. Pingback: Nightmare Market Exit Scam

  8. Pingback: Empire Market

  9. Pingback: ketquabongdatructuyen

  10. The IPCC report suggests that by the end of the century, higher seas are likely to displace or affect 680 million people in low-lying coastal zones and about 65 million citizens of small island states. Strauss said his new study builds off these results.
    https://www.yeah77.com

  11. 카지노 says:

    In what may be a watershed moment in the fight against tuberculosis, the world’s most lethal infectious disease, an experimental new vaccine has protected about half the people who got it, scientists reported Tuesday.
    https://www.dbk222.com

  12. A year ago, when preliminary trial results of the new vaccine were released, the World Health Organization called it “a major scientific breakthrough.”
    https://www.best373.com

  13. BCG, which is not used in the United States, protects infants against some types of tuberculosis, but does not protect adolescents or adults against the form that attacks the lungs, which is the most common type.
    https://www.spacasino.net

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

  15. Pingback: Darknet

  16. Pingback: Darknet Drogen

  17. Pingback: Nachfolger

  18. Pingback: ms88ca

  19. Pingback: w88club

  20. Pingback: kompasqqq

  21. Pingback: www.1mainqq.site

  22. Pingback: ltc qr code generator

Leave a Reply