Sql-server – Time based query help

sql server

I can't seem to get this to work the way I need it to. I have two tables Assignment and Event.

Assignment
StartDate     EndDate       Equipment
4/1/2016      4/23/2016     1001
4/3/2016      4/10/2016     1002
3/30/2016     4/20/2016     1003

Event
StartDate     EndDate       Equipment    Event
4/2/2016      4/3/2016      1001         maintenance
4/8/2016      4/10/2016     1001         maintenance
4/4/2016      4/5/2016      1002         maintenance

I need to join on the equipment and have an output that divides the Assignment using the events. For this sample data I would have an output that looks something like this:

StartDate     EndDate       Equipment    EventStatus
4/1/2016      4/2/2016      1001         Active
4/2/2016      4/3/2016      1001         maintenance
4/3/2016      4/8/2016      1001         Active
4/8/2016      4/10/2016     1001         maintenance
4/10/2016     4/23/2016     1001         Active
4/3/2016      4/4/2016      1002         Active
4/4/2016      4/5/2016      1002         maintenance
4/5/2016      4/10/2016     1002         Active
3/30/2016     4/20/2016     1003         Active

So for Equipment number 1001; it was put into an assignment then had maintenance twice. That maintenance split the assignment into 5 parts like so:

---------------------------------------------------------------------
|  Active  |maintenance|  Active  |maintenance|       Active        |
---------------------------------------------------------------------

Each of those parts must have its own row with the start and end date for that section. How can I write a query to make this work?

Best Answer

Another possible solution: (thanks John Eisbrener for the sample data)

DECLARE @Assignment TABLE
(
    StartDate DATE,
    EndDate DATE,
    Equipment VARCHAR(50)
);

INSERT INTO @Assignment VALUES
    ('4/1/2016', '4/23/2016', '1001')
   ,('4/3/2016', '4/10/2016', '1002')
   ,('3/30/2016', '4/20/2016', '1003');


DECLARE @Event TABLE
(
    EventID INT NOT NULL IDENTITY(1,2),
    StartDate DATE,
    EndDate DATE,
    Equipment VARCHAR(50),
    [Event] VARCHAR(50)
);

INSERT INTO @Event 
    (StartDate, EndDate, Equipment, [Event])
VALUES
    ('4/2/2016', '4/3/2016', '1001', 'maintenance')
   ,('4/8/2016', '4/10/2016', '1001', 'maintenance')
-- Uncomment out prove it still works when stacking events back-to-back
-- ,('4/11/2016', '4/12/2016', '1001', 'maintenance')
   ,('4/4/2016', '4/5/2016', '1002', 'maintenance');

WITH AllEvents AS (

    -- Unpivot all the Assignments
    SELECT Equipment, V.EventDate, V.DateType, 'Active' AS EventType
    FROM @Assignment
    CROSS APPLY (
        VALUES
            (StartDate, 'start'),
            (EndDate, 'end')
    ) AS v(EventDate, DateType)

    UNION ALL 

    -- Unpivot all the Events
    SELECT Equipment, V.EventDate, V.DateType, [Event] AS EventType
    FROM @Event
    CROSS APPLY (
        VALUES
            (StartDate, 'start'),
            (EndDate, 'end')
    ) AS v(EventDate, DateType)

)
SELECT AE.Equipment,
    AE.EventDate AS StartDate,
    NextEvent.EventDate AS EndDate,
    EventStatus = 
        CASE 
            WHEN AE.DateType = 'end' 
                AND AE.EventType = 'maintenance' THEN 'Active'
            ELSE AE.EventType
        END
FROM AllEvents AE
CROSS APPLY (
    -- Next Event by date
    SELECT TOP(1) EventDate, EventType, DateType
    FROM AllEvents
    WHERE Equipment = AE.Equipment
        AND EventDate > AE.EventDate
    ORDER BY EventDate
) AS NextEvent
ORDER BY AE.Equipment, AE.EventDate
Related Question