CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Monah
Monah

Reputation: 6784

partition on range a and b

I have the following table

EmpId   Date    StartOn EndOn
0001    2-Feb-17    590 599
0002    2-Feb-17    600 609
0003    2-Feb-17    610 619
0004    2-Feb-17    626 635
0002    2-Feb-17    630 639
0004    2-Feb-17    640 649
0005    3-Feb-17    630 639

and I want the output to be like this

EmpId   Date    StartOn EndOn   Overlapping Num
0001    2-Feb-17    590 599            0    1
0002    2-Feb-17    600 609            0    1
0003    2-Feb-17    610 619            0    1
0004    2-Feb-17    626 635            1    1
0002    2-Feb-17    630 639            1    2
0004    2-Feb-17    640 649            0    1
0005    3-Feb-17    630 639            0    1

here a sample of data

create table Data(
    EmpId nvarchar(4),
    Date date,
    StartOn int,
    EndOn int
  );

insert into Data(EmpId,Date,StartOn,EndOn)
values('0001','02 Feb 2017',590,599),
('0002','02 Feb 2017',600,609),
('0003','02 Feb 2017',610,619),
('0004','02 Feb 2017',626,635),
('0002','02 Feb 2017',630,639),
('0004','02 Feb 2017',640,649),
('0005','03 Feb 2017',630,639)

Requirements : I want to know how many overlapping occurs between StartOn and EndOn on given date and to sort these overlapping by the StartOn, for example EmpId ( 0004 overlaps with EmpId 0002 between range 626 and 639 ( overlap occurs between range 630 and 635), so Overlapping column will hold the number of overlaps and Num will hold the number of order by StartOn

This what I tried

    ;with overlapping as(
    select a.EmpId,a.Date,a.StartOn,a.EndOn,count(b.EmpId) as Num
    from #Data a
    left join #Data b on a.Date = b.Date
    and a.StartOn<=b.EndOn and a.EndOn>=b.StartOn and b.EmpId<>a.EmpId
    group by a.EmpId,a.Date,a.StartOn,a.EndOn
)
select *,rank() over(partition by Date,Num order by StartOn) as Row
from overlapping
order by Date,StartOn,EmpId

I tried the row_number and rank but failed to get any desired result

Upvotes: 0

Views: 35

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

I would tend to do this with exists rather than a self join. Then, on top of exists, a cumulative sum to define the "groups" and then row_number():

with d as (
      select d.*,
             (case when exists (select 1
                                from #data d2
                                where d2.StartOn <= d.EndOn and
                                      d2.EndOn >= d.StartOn and 
                                      d2.EmpId <> d.EmpId
                               )
                    then 1 else 0
              end) as IsOverlap
      from #data d
     )
select d.*, row_number() over (partition by grp order by StartOn) as num
from (select d.*, sum(IsOverlap) over (order by StartOn) as grp
      from d
     ) d;

However, I'm not 100% sure this meets your needs. What should the results be when there are more overlaps? What happens if an EmpId overlaps with him/her self?

Upvotes: 1

Monah
Monah

Reputation: 6784

I managed to solve it by adding the iif inside the partition

;with overlapping as(
    select a.EmpId,a.Date,a.StartOn,a.EndOn,count(b.EmpId) as Num
    from #Data a
    left join #Data b on a.Date = b.Date
    and a.StartOn<=b.EndOn and a.EndOn>=b.StartOn and b.EmpId<>a.EmpId
    group by a.EmpId,a.Date,a.StartOn,a.EndOn
)
select *,row_number() over(partition by Date,iif(Num=0,StartOn,Num) order by StartOn) as Row
from overlapping
order by Date,StartOn,EmpId

but i am looking for better solution

Upvotes: 0

Zohar Peled
Zohar Peled

Reputation: 82474

Here is one way to do it:

;WITH CTE AS
(
    SELECT  EmpId, 
            [Date], 
            StartOn, 
            EndOn,
            ISNULL((
                SELECT 1
                FROM Data t2 
                WHERE t2.EmpId <> t1.EmpId
                AND t2.[Date] = t1.[Date]
                AND t2.StartOn < t1.EndOn
                AND t2.EndON > t1.StartOn
            ), 0) As Overlapping
    FROM Data t1
)

SELECT  EmpId, 
        [Date], 
        StartOn, 
        EndOn,
        Overlapping,
        ISNULL(NULLIF(Overlapping, 0) + LAG(Overlapping) OVER (ORDER BY [Date], StartOn, EndOn), 0) As Num
FROM CTE

With the sample data you provided, the result is:

EmpId   Date        StartOn     EndOn   Overlapping     Num
0001    02.02.2017  590         599     0               0
0002    02.02.2017  600         609     0               0
0003    02.02.2017  610         619     0               0
0004    02.02.2017  626         635     1               1
0002    02.02.2017  630         639     1               2
0004    02.02.2017  640         649     0               0
0005    03.02.2017  630         639     0               0

Live demo on rextester

Upvotes: 0

Related Questions