基于时间的查询帮助

另一种可能的解决方案：（感谢 John Eisbrener 提供的示例数据）

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

这有点笨拙，因为我无法让窗口函数（即DENSE_RANK、NTILE、RANK或ROW_NUMBER）像我希望的那样工作，所以我不得不通过COALESCE和表结构中的一些自定义来“设计”一个。

这里的基本前提是您首先需要将这些表连接到一个包含所有日期列表的中间表（例如，我创建的另一个表，名为#tmpDates）。这是报告输出的基础，因为我们将对记录进行分组以获得您指定的输出。然后我们在自定义窗口函数中工作（在这里使用 COALESCE），因此枚举了“维护”日和正常日之间的步骤，但为了使其正常工作，我必须在事件表中定义一个带有跳过增量值的标识字段.

下面是代码的所有可怕的荣耀。如果您有任何疑问，请告诉我，我会尽力澄清，但这是垃圾……工作垃圾。我希望有人可以使用排名功能来解决这个问题，而不是我使用 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