SQL and Temporal data

Given a table of appointments, like this:

User     Start                    End
UserA    2016-01-15 12:00:00      2016-01-15 14:00:00    
UserA    2016-01-15 15:00:00      2016-01-15 17:00:00    
UserB    2016-01-15 13:00:00      2016-01-15 15:00:00    
UserB    2016-01-15 13:32:00      2016-01-15 15:00:00    
UserB    2016-01-15 15:30:00      2016-01-15 15:30:00    
UserB    2016-01-15 15:45:00      2016-01-15 16:00:00    
UserB    2016-01-15 17:30:00      2016-01-15 18:00:00    

I want to create a list of distinct time intervals in which the same amount of people have an appointment:

Start                 End                   Count
2016-01-15 12:00:00   2016-01-15 13:00:00   1
2016-01-15 13:00:00   2016-01-15 14:00:00   2
2016-01-15 14:00:00   2016-01-15 15:45:00   1
2016-01-15 15:45:00   2016-01-15 16:00:00   2
2016-01-15 16:00:00   2016-01-15 17:00:00   1
2016-01-15 17:00:00   2016-01-15 17:30:00   0
2016-01-15 17:30:00   2016-01-15 18:00:00   1

How would I do this in SQL, preferably SQL Server 2008?

EDIT: To clarify: Manually, the result is obtained by making one row for each user, marking the blocked time, and then summing up the count of rows that have a mark:

Time  12  13  14  15  16  17
UserA xxxxxxxx    xxxxxxxx
UserB     xxxxxxxx   x      xx
Count 1   2   1      21   0 1

That result set would start at the minimum time available, end at the maximum time available, and while the ASCII art has only a 15min resolution, I would require at least resolution to the minute. I guess you can leave the rows with "0" out of the result, if this is easier for you.

Answers


There's got to be an easier way than this, but at least you can probably follow each step individually:

declare @t table ([User] varchar(19) not null,Start datetime2 not null,[End] datetime2 not null)
insert into @t([User], Start, [End]) values
('UserA','2016-01-15T12:00:00','2016-01-15T14:00:00'),
('UserA','2016-01-15T15:00:00','2016-01-15T17:00:00'),
('UserB','2016-01-15T13:00:00','2016-01-15T15:00:00'),
('UserB','2016-01-15T13:32:00','2016-01-15T15:00:00'),
('UserB','2016-01-15T15:30:00','2016-01-15T15:30:00'),
('UserB','2016-01-15T15:45:00','2016-01-15T16:00:00'),
('UserB','2016-01-15T17:30:00','2016-01-15T18:00:00')

;With Times as (
    select Start as Point from @t
    union
    select [End] from @t
), Ordered as (
    select Point,ROW_NUMBER() OVER (ORDER BY Point) as rn
    from Times
), Periods as (
    select
        o1.Point as Start,
        o2.Point as [End]
    from
        Ordered o1
            inner join
        Ordered o2
            on
                o1.rn = o2.rn - 1
), UserCounts as (
select p.Start,p.[End],COUNT(distinct [User]) as Cnt,ROW_NUMBER() OVER (Order BY p.[Start]) as rn
from
    Periods p
        left join
    @t t
        on
            p.Start < t.[End] and
            t.Start < p.[End]
group by
    p.Start,p.[End]
), Consolidated as (
    select uc.*
    from
        UserCounts uc
            left join
        UserCounts uc_anti
            on
                uc.rn = uc_anti.rn + 1 and
                uc.Cnt = uc_anti.Cnt
    where
        uc_anti.Cnt is null
    union all
    select c.Start,uc.[End],c.Cnt,uc.rn
    from
        Consolidated c
            inner join
        UserCounts uc
            on
                c.Cnt = uc.Cnt and
                c.[End] = uc.Start
)
select
    Start,MAX([End]) as [End],Cnt
from
    Consolidated
group by
    Start,Cnt
order by Start

CTEs are - Times - since any given start or end stamp can start or end a period in the final results, we just get them all in one column - so the Ordered can number them, and so that Periods can then re-assembly them into each smallest possible period.

UserCounts then goes back to the original data and finds out how many Users where overlapped by each calculated period.

Consolidated is the trickiest CTE to follow, but it's basically merging periods that abut each other where the user count is equal.

Results:

Start                       End                         Cnt
--------------------------- --------------------------- -----------
2016-01-15 12:00:00.0000000 2016-01-15 13:00:00.0000000 1
2016-01-15 13:00:00.0000000 2016-01-15 14:00:00.0000000 2
2016-01-15 14:00:00.0000000 2016-01-15 15:45:00.0000000 1
2016-01-15 15:45:00.0000000 2016-01-15 16:00:00.0000000 2
2016-01-15 16:00:00.0000000 2016-01-15 17:00:00.0000000 1
2016-01-15 17:00:00.0000000 2016-01-15 17:30:00.0000000 0
2016-01-15 17:30:00.0000000 2016-01-15 18:00:00.0000000 1

(And I even got the zero row I was unsure I'd be able to conjure into existence)


This kind of query is much easier to write if you have a calendar table. But in this example I've built one on the fly using a recursive CTE. The CTE returns the appointment blocks, which we can then join to the appointment data. I couldn't determine the interval pattern in your sample data, so I've shown the results in blocks of one hour. You could modify this section, or define your own within a second table.

Sample Data

/* Table variables make sharing data easier    
 */
DECLARE @Sample TABLE
    (
        [User]      VARCHAR(50),
        [Start]     DATETIME,
        [End]       DATETIME
    )
;

INSERT INTO @Sample
    (
        [User],
        [Start],
        [End]
    )
VALUES
    ('UserA', '2016-01-15 12:00:00', '2016-01-15 14:00:00'),   
    ('UserA', '2016-01-15 15:00:00', '2016-01-15 17:00:00'),   
    ('UserB', '2016-01-15 13:00:00', '2016-01-15 15:00:00'),
    ('UserB', '2016-01-15 13:32:00', '2016-01-15 15:00:00'), 
    ('UserB', '2016-01-15 15:30:00', '2016-01-15 15:30:00'),
    ('UserB', '2016-01-15 15:45:00', '2016-01-15 16:00:00'),
    ('UserB', '2016-01-15 17:30:00', '2016-01-15 18:00:00')
;

I've used two variables to limit the returned results to just those appointments that fall within the given start and end point.

/* Set an start and end point for the next query    
 */
DECLARE @Start  DATETIME = '2016-01-15 12:00:00';
DECLARE @End    DATETIME = '2016-01-15 18:00:00';

WITH Calendar AS
    (
            /* Anchor returns start of first appointment    
             */
            SELECT
                @Start                                          AS [Start],
                DATEADD(SECOND, -1, DATEADD(HOUR, 1, @Start))   AS [End] 

        UNION ALL

            /* Recursion, keep adding new records until end of last appointment    
             */
            SELECT
                DATEADD(HOUR, 1, [Start])   AS [Start],
                DATEADD(HOUR, 1, [End])     AS [End]
            FROM
                Calendar
            WHERE
                [End] <= @End
    )
SELECT
    c    [Start],
    c    [End],
    COUNT(DISTINCT s    [User]) AS [Count]
FROM
    Calendar AS c
        LEFT OUTER JOIN @Sample AS s            ON s    [Start] BETWEEN c    [Start] AND c    [End]
                                                OR s    [End] BETWEEN c    [Start] AND c    [End]
GROUP BY
    c    [Start],
    c    [End]
;

Because an appointment can exceed one hour it may contribute to more than one row. This explains why 7 sample rows leads to a returned total of 9.


Need Your Help

JavaScript Traverse Table Issue

javascript html html-table children

Why does this script bail out (IE) or stick (FF) at the first table cell containing text that it finds?

Problem with codeigniter's redirect function

php codeigniter redirect header superglobals

This may be a n00b topic but, anyways, I have been having a rather difficult and strange time with this bug. Basically I was working on a controller method for a page that displays a form. Basicall...