3

This is a problem I've been trying to work through on and off for the past month, and haven't made any progress. Essentially, I am working with a HR system that stores two major types of position for employees: Primary (P) and Acting (A). The position types aren't hugely relevant, but are important to know to understand the way the data is structured. An employee will always be in a Primary position, but may be put into an Acting position temporarily while it is vacant, or while the Primary person for that role is on leave.

The way that this is represented in the system is diagrammed below (as an example and not to scale):

P (2018-07-28 - 9999-99-99) <------------------------------------------------------------->
A (2018-09-04 - 2018-09-05)             <--->
A (2018-11-12 - 2018-11-13)                                          <--->
A (2018-11-19 - 2018-11-20)                                                  <--->

Due to a set of data I'm trying to join the position information with, I need it to be intersected to look like this, where the Acting position always takes precedence over the Primary position.

P 2018-07-28 - 2018-09-03 <---------->
A 2018-09-04 - 2018-09-05             <--->
P 2018-09-06 - 2018-11-11                  <----------------------->
A 2018-11-12 - 2018-11-13                                           <--->
P 2018-11-14 - 2018-11-18                                                <----->
A 2018-11-19 - 2018-11-20                                                  
<--->
P 2018-11-21 - 9999-99-99                                                  
     <----->

Below is a representation of the data directly from the database, to give an idea of what it looks like overall:

Employee    Position    Start Date  End Date    Position Type
1           30          2016-12-10  2016-12-11  P
1           30          2016-12-12  2017-12-08  P
1           20          2017-01-31  2017-02-02  A
1           20          2017-03-29  2017-03-31  A
1           20          2017-04-19  2017-04-21  A
1           20          2017-06-22  2017-06-23  A
1           20          2017-06-29  2017-06-30  A
1           20          2017-10-03  2017-10-06  A
1           30          2017-12-09  2018-07-26  P
1           20          2018-02-12  2018-03-02  A
1           20          2018-07-19  2018-07-20  A
1           30          2018-07-27  2018-07-27  P
1           30          2018-07-28  9999-99-99  P
1           20          2018-09-04  2018-09-05  A
1           20          2018-11-12  2018-11-13  A
1           20          2018-11-19  2018-11-20  A

As you can see, there are even multiple entries for the same position where the dates are contiguous. These need to be combined into a contiguous entry until they are intersected/overridden by Acting Positions. I've been scouring different sources on the internet for the past month and haven't even come close to a viable solution. The closest I've come is from SQL – Combining Overlapping Date Rows, which I've converted from Oracle but I don't have enough knowledge of T-SQL to take it all the way to what I need, original Oracle code below:

SELECT
  KEY,
  MIN(TD) MINTD,
  MAX(TD) MAXTD
FROM
  (SELECT
    KEY,
    TD,
    SUM(C) OVER (PARTITION BY KEY ORDER BY TD) SC
  FROM
    (SELECT
      KEY,
      D,
      TD,
      TPD,
      DECODE(SIGN(LTD-TPD),1,1,0) C
    FROM
      (SELECT
        KEY,
        D,
        TRUNC(D,'MM') TD,
        ADD_MONTHS(TRUNC(D,'MM'),-1) LTD,
        LAG(D,1) OVER (PARTITION BY KEY ORDER BY D) PD,
        LAG(TRUNC(D,'MM'),1) OVER (PARTITION BY KEY ORDER BY D) TPD
      FROM
        T1)))
GROUP BY
  KEY,
  SC
ORDER BY
  KEY,
  SC;

Any ideas on a SELECT query to group and intersect the dates as outlined above?

  • Please update your question and include an example of the desired results. – Scott Hodgin Dec 6 '18 at 11:54
  • Also, your current query seems to reference a column called KEY (and other columns) or perhaps this is Oracle syntax, but those column(s) do not exist in your sample data. Please update your question to include table definitions in the form of CREATE statements. – Scott Hodgin Dec 6 '18 at 12:00
  • @ScottHodgin I think the desired result is the second diagram. – McNets Dec 6 '18 at 14:10
3

First, I think the link you had supplied doesn't fit for your question because it is about combining overlapped dates, and you need to generate (GAPs) new dates.

I would divide your data in two groups, those corresponding to Primary(s) and those that belong to Acting(s).

SELECT   t1.*,
         ROW_NUMBER() OVER (PARTITION BY Employee ORDER BY Employee, StartDate) P1
FROM     tbl t1
WHERE    t1.PosType = 'P'
ORDER BY t1.Employee, t1.StartDate
;
GO
Employee | Position | StartDate  | EndDate    | PosType | P1
-------: | -------: | :----------| :----------| :------ | :-
       1 |       30 | 10/12/2016 | 11/12/2016 | P       | 1 
       1 |       30 | 12/12/2016 | 08/12/2017 | P       | 2 
       1 |       30 | 09/12/2017 | 26/07/2018 | P       | 3 
       1 |       30 | 27/07/2018 | 27/07/2018 | P       | 4 
       1 |       30 | 28/07/2018 | 31/12/9999 | P       | 5 

Next Acting rows are subsumed into the second Principal row: 12/12/2016 | 08/12/2017

SELECT   t2.*,
         ROW_NUMBER() OVER (ORDER BY t2.StartDate) P2
FROM     tbl t2
WHERE    t2.PosType = 'A'
         AND t2.StartDate >= '20161212'
         AND t2.StartDate <= '20171208'
ORDER BY t2.Employee, t2.StartDate
;
GO
Employee | Position | StartDate  | EndDate    | PosType | P2
-------: | -------: | :----------| :----------| :------ | :--
       1 |       20 | 31/01/2017 | 02/02/2017 | A       | 1    
       1 |       20 | 29/03/2017 | 31/03/2017 | A       | 2    
       1 |       20 | 19/04/2017 | 21/04/2017 | A       | 3    
       1 |       20 | 22/06/2017 | 23/06/2017 | A       | 4    
       1 |       20 | 29/06/2017 | 30/06/2017 | A       | 5    
       1 |       20 | 03/10/2017 | 06/10/2017 | A       | 6    

Now let me generate a new dataset, for each Primary I'll add the corresponding Acting(s) using an OUTER JOIN, this outer join returns all Primary(s) even if they haven't subsumed Acting(s).

SELECT   t1.*, COALESCE(t3.P2, 0) P2, P, t3.PrevED, t3.SD, t3.ED, NextSD, t3.PT
INTO #mydata
FROM     (SELECT *,
                 ROW_NUMBER() OVER (PARTITION BY Employee ORDER BY Employee, StartDate) as P1
          FROM   tbl 
          WHERE  PosType = 'P'
          ) t1
OUTER APPLY (SELECT   ROW_NUMBER() OVER (ORDER BY t2.StartDate) as P2,
                      LAG(t2.EndDate) OVER (ORDER BY t2.StartDate) as PrevED,
                      LEAD(t2.StartDate) OVER (ORDER BY t2.StartDate) as NextSD,
                      t2.Position as P, t2.StartDate as SD, t2.EndDate as ED, t2.PosType as PT
             FROM     tbl t2
             WHERE    t2.Employee = t1.Employee
                      AND t2.PosType = 'A'
                      AND t2.StartDate >= t1.StartDate
                      AND t2.StartDate <= t1.EndDate
            ) t3
WHERE    t1.PosType = 'P'
ORDER BY t1.Employee, t1.StartDate, t3.SD
;

CREATE CLUSTERED INDEX PK_Mydata ON #mydata(Employee, P1, P2);

SELECT * FROM #mydata ORDER BY Employee, P1, P2;
GO

NOTE: I've used a temporary table plus and index, but you could try by using a CTE. You should check performance of both options.

This is the new scenario:

Employee | Position | StartDate  | EndDate    | PosType | P1 | P2 |    P | PrevED     | SD         | ED         | NextSD     | PT  
-------: | -------: | :----------| :----------| :------ | :- | :- | ---: | :----------| :----------| :----------| :----------| :---
       1 |       30 | 10/12/2016 | 11/12/2016 | P       | 1  | 0  | null | null       | null       | null       | null       | null
       1 |       30 | 12/12/2016 | 08/12/2017 | P       | 2  | 1  |   20 | null       | 31/01/2017 | 02/02/2017 | 29/03/2017 | A   
       1 |       30 | 12/12/2016 | 08/12/2017 | P       | 2  | 2  |   20 | 02/02/2017 | 29/03/2017 | 31/03/2017 | 19/04/2017 | A   
       1 |       30 | 12/12/2016 | 08/12/2017 | P       | 2  | 3  |   20 | 31/03/2017 | 19/04/2017 | 21/04/2017 | 22/06/2017 | A   
       1 |       30 | 12/12/2016 | 08/12/2017 | P       | 2  | 4  |   20 | 21/04/2017 | 22/06/2017 | 23/06/2017 | 29/06/2017 | A   
       1 |       30 | 12/12/2016 | 08/12/2017 | P       | 2  | 5  |   20 | 23/06/2017 | 29/06/2017 | 30/06/2017 | 03/10/2017 | A   
       1 |       30 | 12/12/2016 | 08/12/2017 | P       | 2  | 6  |   20 | 30/06/2017 | 03/10/2017 | 06/10/2017 | null       | A   
       1 |       30 | 09/12/2017 | 26/07/2018 | P       | 3  | 1  |   20 | null       | 12/02/2018 | 02/03/2018 | 19/07/2018 | A   
       1 |       30 | 09/12/2017 | 26/07/2018 | P       | 3  | 2  |   20 | 02/03/2018 | 19/07/2018 | 20/07/2018 | null       | A   
       1 |       30 | 27/07/2018 | 27/07/2018 | P       | 4  | 0  | null | null       | null       | null       | null       | null
       1 |       30 | 28/07/2018 | 31/12/9999 | P       | 5  | 1  |   20 | null       | 04/09/2018 | 05/09/2018 | 12/11/2018 | A   
       1 |       30 | 28/07/2018 | 31/12/9999 | P       | 5  | 2  |   20 | 05/09/2018 | 12/11/2018 | 13/11/2018 | 19/11/2018 | A   
       1 |       30 | 28/07/2018 | 31/12/9999 | P       | 5  | 3  |   20 | 13/11/2018 | 19/11/2018 | 20/11/2018 | null       | A   

I've added two partitions P1, P2 and I set up an index on them, these partitions will help me to filter information in this new scenario according to 4 types of rows:

|---------------------------------|   Type 1
|--P--|                               Type 2
       |--A--|                        Type 3
              |--P--|                 Type 2
                     |--A--|          Type 3
                            |--P--|   Type 4

Type 1:

Primary(s) that don't have any subsumed Acting.

  • P2 = 0

Type 2:

If exists, left gaps between Acting(s) plus between first Acting and Primary start date.

  • P2 <> 0
  • Actign start date - 1 > Primary start date/Acting end date

Type 3:

All Acting preriods.

  • P2 <> 0

Type 4:

If exists, right gaps between last Acting and Primary end date.

  • P2 <> 0
  • NextSD IS NULL (Last Acting)
  • Acting end date < Primary end date

Now the selection:

SELECT *
FROM
    (
    -- Primary(s) without Acting(s)
    SELECT Employee, Position, StartDate, EndDate, PosType
    FROM   #mydata
    WHERE  Employee = 1
           AND P2 = 0
    --
    -- plus all Acting(s)
    --
    UNION
    SELECT Employee, P, SD, ED, PT
    FROM   #mydata
    WHERE  Employee = 1
           AND P2 <> 0
    --
    -- plus all (left) Gaps
    --
    UNION
    SELECT Employee, 
           Position, 
           IIF(PrevED IS NULL, StartDate, DATEADD(day, 1, PrevED)), 
           DATEADD(day, -1, SD),
           PosType
    FROM   #mydata
    WHERE  Employee = 1
           AND P2 <> 0
           AND DATEDIFF(day, IIF(PrevED IS NULL, StartDate, DATEADD(day, 1, PrevED)), SD) > 0
    UNION
    --
    -- plus all (last right) Gap
    --
    SELECT Employee, 
           Position, 
           DATEADD(day, 1, ED),
           EndDate,
           PosType
    FROM   #mydata
    WHERE  Employee = 1
           AND P2 <> 0
           AND NextSD IS NULL
           AND DATEDIFF(day, ED, EndDate) > 0
    ) T
ORDER BY 1, 3;
GO

This is the final result:

Employee | Position | StartDate  | EndDate    | PosType
-------: | -------: | :----------| :----------| :------
       1 |       30 | 10/12/2016 | 11/12/2016 | P      
       1 |       30 | 12/12/2016 | 30/01/2017 | P      
       1 |       20 | 31/01/2017 | 02/02/2017 | A      
       1 |       30 | 03/02/2017 | 28/03/2017 | P      
       1 |       20 | 29/03/2017 | 31/03/2017 | A      
       1 |       30 | 01/04/2017 | 18/04/2017 | P      
       1 |       20 | 19/04/2017 | 21/04/2017 | A      
       1 |       30 | 22/04/2017 | 21/06/2017 | P      
       1 |       20 | 22/06/2017 | 23/06/2017 | A      
       1 |       30 | 24/06/2017 | 28/06/2017 | P      
       1 |       20 | 29/06/2017 | 30/06/2017 | A      
       1 |       30 | 01/07/2017 | 02/10/2017 | P      
       1 |       20 | 03/10/2017 | 06/10/2017 | A      
       1 |       30 | 07/10/2017 | 08/12/2017 | P      
       1 |       30 | 09/12/2017 | 11/02/2018 | P      
       1 |       20 | 12/02/2018 | 02/03/2018 | A      
       1 |       30 | 03/03/2018 | 18/07/2018 | P      
       1 |       20 | 19/07/2018 | 20/07/2018 | A      
       1 |       30 | 21/07/2018 | 26/07/2018 | P      
       1 |       30 | 27/07/2018 | 27/07/2018 | P      
       1 |       30 | 28/07/2018 | 03/09/2018 | P      
       1 |       20 | 04/09/2018 | 05/09/2018 | A      
       1 |       30 | 06/09/2018 | 11/11/2018 | P      
       1 |       20 | 12/11/2018 | 13/11/2018 | A      
       1 |       30 | 14/11/2018 | 18/11/2018 | P      
       1 |       20 | 19/11/2018 | 20/11/2018 | A      
       1 |       30 | 21/11/2018 | 31/12/9999 | P      

db<>fiddle here

  • This has worked brilliantly and integrated well into the problem I was trying to solve. I had gotten to the point where I was modifying code which was beyond wrong. I did hit one hiccup in integrating it into my code, and that was the WHERE Employee = 1 in the last section of code. Once I'd removed that, it worked perfectly! Thanks ever so much! – Xaedian Dec 7 '18 at 7:58
  • I'm glad to be able to help. BTW maybe you could get better performance using a cursor. – McNets Dec 7 '18 at 8:05
0

If your end date is inclusive, the results could be further packed together. Here is a solution in pl-sql:

drop table tbl;

create table tbl (
    employee integer, 
    position integer, 
    begin Date, 
    end Date,           -- inclusive date
    posType varchar(1)
);

insert all
into tbl values (1, 30, to_date('20161210', 'yyyyMMdd'), to_date('20161211', 'yyyyMMdd'), 'P')
into tbl values (1, 30, to_date('20161212', 'yyyyMMdd'), to_date('20171208', 'yyyyMMdd'), 'P')
into tbl values (1, 20, to_date('20170131', 'yyyyMMdd'), to_date('20170202', 'yyyyMMdd'), 'A')
into tbl values (1, 20, to_date('20170329', 'yyyyMMdd'), to_date('20170331', 'yyyyMMdd'), 'A')
into tbl values (1, 20, to_date('20170419', 'yyyyMMdd'), to_date('20170421', 'yyyyMMdd'), 'A')
into tbl values (1, 20, to_date('20170622', 'yyyyMMdd'), to_date('20170623', 'yyyyMMdd'), 'A')
into tbl values (1, 20, to_date('20170629', 'yyyyMMdd'), to_date('20170630', 'yyyyMMdd'), 'A')
into tbl values (1, 20, to_date('20171003', 'yyyyMMdd'), to_date('20171006', 'yyyyMMdd'), 'A')
into tbl values (1, 30, to_date('20171209', 'yyyyMMdd'), to_date('20180726', 'yyyyMMdd'), 'P')
into tbl values (1, 20, to_date('20180212', 'yyyyMMdd'), to_date('20180302', 'yyyyMMdd'), 'A')
into tbl values (1, 20, to_date('20180719', 'yyyyMMdd'), to_date('20180720', 'yyyyMMdd'), 'A')
into tbl values (1, 30, to_date('20180727', 'yyyyMMdd'), to_date('20180727', 'yyyyMMdd'), 'P')
into tbl values (1, 30, to_date('20180728', 'yyyyMMdd'), to_date('99991231', 'yyyyMMdd'), 'P')
into tbl values (1, 20, to_date('20180904', 'yyyyMMdd'), to_date('20180905', 'yyyyMMdd'), 'A')
into tbl values (1, 20, to_date('20181112', 'yyyyMMdd'), to_date('20181113', 'yyyyMMdd'), 'A')
into tbl values (1, 20, to_date('20181119', 'yyyyMMdd'), to_date('20181120', 'yyyyMMdd'), 'A')
select 0 from dual;

with intersect_0 as (
    -- intersects all date ranges without gaps
    select s.employee, s.begin, min(e.end) end from (
        select employee, begin from tbl
        union 
        select employee, decode(   end, to_date('99991231', 'yyyyMMdd'), cast(null as date),   end + 1) from tbl
    ) s
    inner join (
        select employee, end from tbl
        union 
        select employee, decode( begin, to_date('00010101', 'yyyyMMdd'), cast(null as date), begin - 1) from tbl
    ) e on e.end >= s.begin 
        and s.employee = e.employee
    where s.begin is not null 
    and e.end is not null
    group by s.employee, s.begin
)
, relevant_0 as (
    -- only take relevant date ranges
    select r.*, position from (
        select employee, begin, end, posType from (
            select i.employee, i.begin, i.end, 
                case 
                    when count(case when b.posType = 'A' then 1 end) > 0 then 'A'
                    when count(case when b.posType = 'P' then 1 end) > 0 then 'P'
                    else null
                end posType
            from intersect_0 i
            inner join tbl b on b.begin <= i.end 
                and i.begin <= b.end 
                and i.employee = b.employee
            group by i.employee, i.begin, i.end
        ) 
        where posType is not null
    ) r
    cross apply (
        select min(b.position) keep (dense_rank first order by b.position) position 
        from tbl b 
        where b.begin <= r.end
        and r.begin <= b.end 
        and r.employee = b.employee
        and r.posType = b.posType
        group by b.employee
    )
)
, packed_0 as (
    -- pack continuous relevant date ranges
    select * from relevant_0
    match_recognize (
      partition by employee
      order by end, begin
      measures
        min(begin) begin,
        last(end) end,
        last(posType) posType
      pattern (a* b) 
      define
        a as end >= next(begin - 1) 
            and a.posType = next(posType)
    )
)
select * from packed_0
order by employee, begin, end;
  • 1
    Have you noticed that the question had been tagged with sql-server? – ypercubeᵀᴹ Apr 28 at 16:04
  • I added an answer in t-sql below. – dfhwze Apr 28 at 16:54
0

Finding continuous periods by employee, by position type (preferring Acting over Primary) in T-SQL:

Fiddle

Results:

Employee  StartDate      EndDate     Position    PosType
       1  2016-12-10     2017-01-30        30    P
       1  2017-01-31     2017-02-02        20    A
       1  2017-02-03     2017-03-28        30    P
       1  2017-03-29     2017-03-31        20    A
       1  2017-04-01     2017-04-18        30    P
       1  2017-04-19     2017-04-21        20    A
       1  2017-04-22     2017-06-21        30    P
       1  2017-06-22     2017-06-23        20    A
       1  2017-06-24     2017-06-28        30    P
       1  2017-06-29     2017-06-30        20    A
       1  2017-07-01     2017-10-02        30    P
       1  2017-10-03     2017-10-06        20    A
       1  2017-10-07     2018-02-11        30    P
       1  2018-02-12     2018-03-02        20    A
       1  2018-03-03     2018-07-18        30    P
       1  2018-07-19     2018-07-20        20    A
       1  2018-07-21     2018-09-03        30    P
       1  2018-09-04     2018-09-05        20    A
       1  2018-09-06     2018-11-11        30    P
       1  2018-11-12     2018-11-13        20    A
       1  2018-11-14     2018-11-18        30    P
       1  2018-11-19     2018-11-20        20    A
       1  2018-11-21     9999-12-31        30    P
23 rows

SQL:

create table tbl (Employee int, Position int, StartDate Date, [EndDate] Date, PosType char(1));
insert into tbl values
(1, 30, '20161210', '20161211', 'P'),
(1, 30, '20161212', '20171208', 'P'),
(1, 20, '20170131', '20170202', 'A'),
(1, 20, '20170329', '20170331', 'A'),
(1, 20, '20170419', '20170421', 'A'),
(1, 20, '20170622', '20170623', 'A'),
(1, 20, '20170629', '20170630', 'A'),
(1, 20, '20171003', '20171006', 'A'),
(1, 30, '20171209', '20180726', 'P'),
(1, 20, '20180212', '20180302', 'A'),
(1, 20, '20180719', '20180720', 'A'),
(1, 30, '20180727', '20180727', 'P'),
(1, 30, '20180728', '99991231', 'P'),
(1, 20, '20180904', '20180905', 'A'),
(1, 20, '20181112', '20181113', 'A'),
(1, 20, '20181119', '20181120', 'A');

with intersect_0 (Employee, StartDate, EndDate) as (
    -- intersects all date ranges without gaps
    select s.Employee, s.StartDate, min(e.EndDate) EndDate from (
        select Employee, StartDate from tbl
        union 
        select Employee, case when EndDate = '99991231' 
            then null else dateadd(d, 1, EndDate) end StartDate from tbl
    ) s
    inner join (
        select Employee, EndDate from tbl
        union 
        select Employee, case when StartDate ='00010101' 
            then null else dateadd(d, -1, StartDate) end EndDate from tbl
    ) e on e.EndDate >= s.StartDate 
        and s.Employee = e.Employee
    where s.StartDate is not null 
    and e.EndDate is not null
    group by s.Employee, s.StartDate
)
, relevant_0 as (
    -- only take relevant date ranges
    select r.*, r1.Position from (
        select Employee, StartDate, EndDate, PosType from (
            select i.Employee, i.StartDate, i.EndDate, 
                case 
                    when count(case when b.PosType = 'A' then 1 end) > 0 then 'A'
                    when count(case when b.PosType = 'P' then 1 end) > 0 then 'P'
                    else null
                end PosType
            from intersect_0 i
            inner join tbl b on b.StartDate <= i.EndDate 
                and i.StartDate <= b.EndDate 
                and i.Employee = b.Employee
            group by i.Employee, i.StartDate, i.EndDate
        ) r0
        where PosType is not null
    ) r
    cross apply (
        select Position from (
            select b.Position, row_number() over (partition by Employee order by StartDate) as Rnk
            from tbl b 
            where b.StartDate <= r.EndDate
            and r.StartDate <= b.EndDate 
            and r.Employee = b.Employee
            and r.PosType = b.PosType
        ) r2
        where Rnk = 1
    ) r1
)
, packed_0 as (
    -- pack continuous relevant date ranges
    select Employee, min(StartDate) StartDate, max(EndDate) EndDate, min(Position) Position, min(PosType) PosType from (
        select Employee, StartDate, EndDate, Position, PosType, sum(GroupStart) over (partition by Employee order by StartDate) Grp from (
            select Employee, StartDate, EndDate, Position, PosType
                , case when max(EndDate) 
                            over (partition by Employee order by EndDate, StartDate rows between unbounded preceding and 1 preceding)
                                    >= case when StartDate ='00010101' then null else dateadd(d, -1, StartDate) end
                        and last_value(PosType) 
                            over (partition by Employee order by EndDate, StartDate rows between unbounded preceding and 1 preceding)
                                    = PosType
                    then 0 else 1 end GroupStart
            from relevant_0
        ) p0
    ) p1
    group by Employee, Grp
)
select Employee, 
    convert(varchar, StartDate, 23) StartDate, 
    convert(varchar, EndDate, 23) EndDate, 
    Position, PosType 
    from packed_0;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.