Ajuda de consulta baseada em tempo

Outra solução possível: (obrigado John Eisbrener pelos dados de amostra)

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

Isso é um pouco desajeitado, pois não consegui fazer uma função de janelamento (ou seja , DENSE_RANK , NTILE , RANK ou ROW_NUMBER ) funcionar como eu esperava, então tive que "projetar" uma via COALESCE e algumas personalizações na estrutura da tabela.

A premissa básica aqui é que primeiro você precisa unir essas tabelas a uma tabela intermediária que contém uma lista de todos os dias (por exemplo, uma tabela adicional que criei, chamada #tmpDates). Essa é a base para a saída do relatório, pois agruparemos os registros para obter a saída especificada. Em seguida, trabalhamos na função de janela personalizada (usando COALESCE aqui) para que as etapas entre os dias de "manutenção" e os dias normais sejam enumeradas, mas para que funcione corretamente, tive que definir um campo de identificação na tabela de eventos com um valor de incremento de salto .

O código em toda a sua glória horrenda está abaixo. Se você tiver dúvidas, me avise e farei o possível para esclarecer, mas isso é lixo ... lixo de trabalho. Espero que alguém consiga fazer isso funcionar com uma função de classificação em vez do meu horrível festival de hack-and-slash com COALESCE:

-- Build out Tables to Show Example
CREATE TABLE #tmpAssignment
(
    StartDate DATE,
    EndDate DATE,
    Equipment VARCHAR(50)
)

INSERT INTO #tmpAssignment VALUES
    ('4/1/2016', '4/23/2016', '1001')
   ,('4/3/2016', '4/10/2016', '1002')
   ,('3/30/2016', '4/20/2016', '1003')

-- NOTE: Make sure the identity field Skips a number to allow for windowing in the report
CREATE TABLE #tmpEvent
(
    EventID INT NOT NULL IDENTITY(1,2),
    StartDate DATE,
    EndDate DATE,
    Equipment VARCHAR(50),
    [Event] VARCHAR(50)
)

INSERT INTO #tmpEvent 
    (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')

-- Create a Join Table comprising of Dates
CREATE TABLE #tmpDates
(
    DateField   DATE
)

DECLARE @dField DATE
SET @dField = '3/15/2016'

SET NOCOUNT ON

WHILE @dField < '5/15/2016'
BEGIN
    INSERT INTO #tmpDates VALUES (@dField)
    SET @dField = DATEADD(DAY, 1, @dField)
END

SET NOCOUNT OFF


;WITH CTE_report
AS
(
    SELECT  d.DateField, a.Equipment, --e.[Event], e.EventID
        COALESCE(e.[Event], 'none') AS [Event], 
        COALESCE(
            -- Pull Event if Occurred
            e.EventID, 
            -- Window Before or In Between Events - This is horribly inefficient
            (SELECT MIN(EventID) - 1 FROM #tmpEvent te WHERE d.DateField < te.StartDate AND a.Equipment = te.Equipment), 
            -- Final Window After All Events - This is also horribly inefficient
            (SELECT MAX(EventID) + 1 FROM #tmpEvent te WHERE a.Equipment = te.Equipment), 
            -- No Events for a Given Assignment
            0) AS EventID
    FROM    #tmpDates d
            LEFT OUTER JOIN #tmpAssignment a
                ON d.DateField >= a.StartDate
                    AND d.DateField <= a.EndDate
            LEFT OUTER JOIN #tmpEvent e
                ON d.DateField >= e.StartDate
                    AND d.DateField <= e.EndDate
                    AND a.Equipment = e.Equipment
    WHERE   a.Equipment IS NOT NULL
)
SELECT  MIN(DateField) AS StartDate, MAX(DateField) AS EndDate, Equipment, [Event]
FROM    CTE_report
GROUP BY Equipment, [Event], [EventID]
ORDER BY 3, 1


-- Cleanup
DROP TABLE #tmpAssignment
DROP TABLE #tmpEvent
DROP TABLE #tmpDates