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
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?
*******************************************************************
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
Hi Hernan Perez Andrade,
Thanks for your correct solution.. 🙂
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
… [Trackback]
[…] Read More Info here on that Topic: techmixing.com/2015/08/question-of-month-july.html […]
… [Trackback]
[…] Here you will find 26653 additional Info to that Topic: techmixing.com/2015/08/question-of-month-july.html […]
… [Trackback]
[…] Find More Information here on that Topic: techmixing.com/2015/08/question-of-month-july.html […]
… [Trackback]
[…] Read More here to that Topic: techmixing.com/2015/08/question-of-month-july.html […]
… [Trackback]
[…] Read More Info here to that Topic: techmixing.com/2015/08/question-of-month-july.html […]
… [Trackback]
[…] There you will find 41222 additional Info to that Topic: techmixing.com/2015/08/question-of-month-july.html […]
… [Trackback]
[…] Find More Info here to that Topic: techmixing.com/2015/08/question-of-month-july.html […]
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
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
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
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
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
… [Trackback]
[…] Find More on that Topic: techmixing.com/2015/08/question-of-month-july.html […]
… [Trackback]
[…] Read More on that Topic: techmixing.com/2015/08/question-of-month-july.html […]
… [Trackback]
[…] Read More Information here to that Topic: techmixing.com/2015/08/question-of-month-july.html […]
… [Trackback]
[…] Find More Info here on that Topic: techmixing.com/2015/08/question-of-month-july.html […]
… [Trackback]
[…] Here you can find 98092 more Information to that Topic: techmixing.com/2015/08/question-of-month-july.html […]
… [Trackback]
[…] Read More Info here on that Topic: techmixing.com/2015/08/question-of-month-july.html […]
… [Trackback]
[…] Find More on on that Topic: techmixing.com/2015/08/question-of-month-july.html […]
… [Trackback]
[…] Read More Information here on that Topic: techmixing.com/2015/08/question-of-month-july.html […]