Intersection and consolidation of time periods in SQL

Question

I want to achieve similar function that is available in Time Period Library for .NET, but in SQL.

First, I have a table with several rows with an Start Date and an End Date, and I want to consolidate them together like this:

Then with that result and another coming from a different table, I want to find out the intersection between the two of them, like this but only 2 inputs (find the periods that are present in both):

Once I have the intersection is just summing up the time on it.

Here I provide a SQL Fiddle with the expected output with an example:

http://sqlfiddle.com/#!18/504fa/3

Your fiddle provides only a sum of days as a result!? Is this really the expected output? In this case a simple datediff between min(valid_from) and max(valid_to) should do the trick... — Tyron78, Feb 02 '18 at 08:46
By the way: somehow your expected intersect result doesn't fit the date provides... you have doc ids 1 to 4 and the intersect result combines doc id 1 and 2 and doc id 3 and 4... — Tyron78, Feb 02 '18 at 08:57
@Tyron78 it combines doc id 1 and 2 because they overlap . Docs id 3 and 4 are from another day! — Pinx0, Feb 02 '18 at 09:25
@Tyron78 the final result is the sum yes, but first there are calculations to be made, it is not just as simple as you say... — Pinx0, Feb 02 '18 at 09:26
That library looks like it does a lot of neat stuff! Have you tried interfacing with it with the CLR capabilities in SQL Server? Assuming it's possible, you should be able to make a thin wrapper around the library and just call its methods directly from SQL. — Ben Thul, Feb 02 '18 at 17:42

score 3 · Accepted Answer · answered Feb 02 '18 at 15:49

Sample data preparation

CREATE TABLE TableToCombine
    ([IdDoc] int IDENTITY(1,1), [IdEmployee] int, [StartDate] datetime, [EndDate] datetime)
;

INSERT INTO TableToCombine
    (IdEmployee, StartDate, EndDate)
VALUES
    (1, '2018-01-01 06:00:00', '2018-01-01 14:00:00'),
    (2, '2018-01-01 11:00:00', '2018-01-01 19:00:00'),
    (3, '2018-01-01 20:00:00', '2018-01-02 03:00:00'),
    (1, '2018-01-02 06:00:00', '2018-01-02 14:00:00'),
    (2, '2018-01-02 11:00:00', '2018-01-02 19:00:00')
;

CREATE TABLE TableToIntersect
    ([IdDoc] int IDENTITY(1,1), [OrderId] int, [StartDate] datetime, [EndDate] datetime)
;

INSERT INTO TableToIntersect
    (OrderId, StartDate, EndDate)
VALUES
    (1, '2018-01-01 09:00:00', '2018-01-02 12:00:00')
;

Query:

with ExpectedCombineOutput as (
    select
        grp, StartDate = min(StartDate), EndDate = max(EndDate)
    from (
        select
            *, sum(iif(cd between StartDate and EndDate, 0, 1))over(order by StartDate) grp
        from (
            select
                *, lag(EndDate) over (order by IdDoc) cd
            from
                TableToCombine
        ) t
    ) t
    group by grp
)

select 
    a.grp, StartDate = iif(a.StartDate < b.StartDate, b.StartDate, a.StartDate)
    , EndDate = iif(a.EndDate < b.EndDate, a.EndDate, b.EndDate)
from
    ExpectedCombineOutput a
    join TableToIntersect b on a.StartDate <= b.EndDate and a.EndDate >= b.StartDate

Intersecting time intervals are combined in CTE. And then joined with your intersectTable to find overlapping periods. Two periods overlap if a.StartDate < b.EndDate and a.EndDate > b.StartDate

Raska · Answer 2 · 2018-02-06T07:49:54.463

PinX0, I am not sure if I have totally understood your question/ requirement. Following query gives you result for ExpectedIntersectOutput:-

Insert into #ExpectedCombineOutput
select distinct min(startdate), max(enddate) from 
(
Select tc.IdDoc as IdDoc, tcw.IdDoc as SecIdDoc, 
case when (tc.startdate between tcw.startdate and tcw.enddate) then tcw.startdate else tc.StartDate end as StartDate,
case when (tc.enddate between tcw.startdate and tcw.enddate) then tcw.enddate else tc.EndDate end as EndDate
from #TableToCombine tc
left join #TableToCombine tcw on tc.IdDoc <> tcw.IdDoc
) test group by IdDoc

Insert into #ExpectedIntersectOutput
Select case when ti.startdate <= tc.startdate then tc.startdate else ti.startdate end as 'StartDate',
       case when ti.enddate >= tc.enddate then tc.enddate else ti.enddate end as 'EndDate'
from #TableToIntersect ti
inner join #ExpectedCombineOutput tc on 1=1

Your code worked, but it did by chance. I added some complexity to the sample to break it. — Pinx0, Feb 02 '18 at 12:11
Please add all scenarios in the sample data...it will be easier to give a solution. For your updated / added sample data, please check the edited code. — Raska, Feb 05 '18 at 10:20

Puser · Answer 3 · 2019-12-26T17:12:09.010

here is a working example in MySQL. I was using a simple view for this.

Let us consider a fitness with customers and employees. You need to know how many hours were spent by customers when an employee was present.

First let you prepare a testing table:

CREATE TABLE Customer
(id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
 customerId int NOT NULL,
 arrival datetime,
 leaving datetime);

INSERT INTO Customer
    (customerId, arrival, leaving)
VALUES
    (1, '2018-01-01 06:00:00', '2018-01-01 14:00:00'),
    (2, '2018-01-01 11:00:00', '2018-01-01 19:00:00'),
    (3, '2018-01-01 20:00:00', '2018-01-02 03:00:00'),
    (1, '2018-01-02 06:00:00', '2018-01-02 14:00:00'),
    (2, '2018-01-02 11:00:00', '2018-01-02 19:00:00')
;

CREATE TABLE Employee
    (id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
     employeeId int NOT NULL,
     arrival datetime,
     leaving datetime)
;

INSERT INTO Employee
    (employeeId, arrival, leaving)
VALUES
    (1, '2018-01-01 09:00:00', '2018-01-01 12:00:00',),
    (2, '2018-01-01 11:30:00', '2018-01-01 20:00:00')
;

When you have a table, let you create a view with time intersections:

CREATE OR REPLACE VIEW intersectionTimeView AS select e.employeeId, 
c.customerId,
IF(c.arrival>e.arrival, c.arrival, e.arrival) AS arrivalMax,
IF(c.leaving>e.leaving, e.leaving, c.leaving) AS leavingMin
FROM  Customer c, Employee e
WHERE TIMEDIFF(c.arrival,e.leaving)<=0
AND  TIMEDIFF(c.leaving,e.arrival)>=0

And finally and easily you can get the hours by:

SELECT employeeId, SUM( timestampdiff(minute,arrivalMax,leavingMin)/60) as summ
FROM intersectionTimeView WHERE employeeId=2 
GROUP BY employeeId

Intersection and consolidation of time periods in SQL

3 Answers3

Linked