日期差异，T，多行售票系统

Question

我正在尝试获取一个存储过程来计算票证打开的时间，包括多次关闭和打开。我的数据如下：

      FeedbackID    Open/Closed TimeStamp
      2145          Open        2015-11-16 20:23:49.750
      2145          Closed      2015-11-23 12:00:35.087
      2146          Open        2015-11-16 21:44:59.020
      2146          Closed      2015-11-17 12:24:55.843
      2146          Open        2015-12-04 13:43:41.830
      2146          Closed      2015-12-04 13:45:04.410
      2147          Open        2015-11-17 02:39:41.263
      2147          Closed      2015-11-23 22:11:33.490

如您所见，FeedbackID #2146有4个事件。我需要能够计算出每个开放和封闭事件之间的差异，然后将它们相加，如果有超过2个开放和关闭事件。任何帮助都是非常感谢的！

编辑:发布以下过程的代码

    ALTER PROCEDURE [dbo].[spGetOpenCloseFeedbackEvents]    
    AS
    BEGIN

    DECLARE @TempTable TABLE (FeedbackID int, [Open/Closed] varchar(20), [TimeStamp] datetime)
    DECLARE @UIDTable TABLE (FeedBackID int, [UID] uniqueidentifier)
    DECLARE @Open Table (FeedbackID int, [Open/Closed] varchar(20), [TimeStamp] datetime)
    DECLARE @Closed Table (FeedbackID int, [Open/Closed] varchar(20), [TimeStamp] datetime)

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

INSERT INTO @TempTable (FeedbackID, [Open/Closed], [TimeStamp])
SELECT * FROM dbo.FeedbackChange
WHERE FeedbackID = FeedbackChange.FeedbackID
AND
[Open/Closed] IS NOT NULL
ORDER BY FeedbackID

INSERT INTO @UIDTable (FeedBackID, [UID])
SELECT FeedbackID, [UID] FROM tblFeedbackRequests fbr
where fbr.FeedbackID = FeedbackID

SELECT * FROM @TempTable t
--JOIN @UIDTable u ON t.FeedbackID = u.FeedBackID
--WHERE u.UID = @UID
Order by t.FeedbackID

SELECT FeedbackID, [Open/Closed], TimeStamp, 
       ROW_NUMBER() over (partition by FeedBackID order by TimeStamp asc) as [Indicator]
INTO @Open
FROM @TempTable
Where [Open/Closed] = 'Open'

SELECT FeedbackID, [Open/Closed], TimeStamp, 
       ROW_NUMBER() over (partition by FeedBackID order by TimeStamp asc) as [Indicator]
INTO @Closed
FROM @TempTable
Where [Open/Closed] = 'Closed'

SELECT SUM(DATEDIFF(day,o.TimeStamp,c.TimeStamp)) as "Days Open",
       o.FeedbackID
FROM   @Open o
INNER JOIN @Closed c on o.FeedbackID = c.FeedbackID and o.[Indicator = c.Indicator
GROUP BY   o.FeedbackID

结束

Answer 1

首先，为打开的状态创建临时表或CTE，为关闭的状态创建另一个。使用ROW_NUMBER over (partition by FeedBackID order by TimeStamp asc)创建一个指示符，其中打开的状态记录与哪个已关闭的记录匹配：

SELECT FeedbackID, [Open/Closed], TimeStamp, 
       ROW_NUMBER over (partition by FeedBackID order by TimeStamp asc) as "Indicator"
INTO @Open
FROM Table
WHERE [Open/Closed] = 'Open'

SELECT FeedbackID, [Open/Closed], TimeStamp, 
       ROW_NUMBER over (partition by FeedBackID order by TimeStamp asc) as "Indicator"
INTO @Closed
FROM Table
WHERE [Open/Closed] = 'Closed'

然后将这些表JOIN在FeedbackID和Indicator上，并进行日期聚合。这将使我们能够确保最早打开的记录与每个FeedbackID的最早关闭记录匹配。

SELECT     SUM(DATEDIFF(day,o.TimeStamp,c.TimeStamp)) as "Days Open",
           o.FeedbackID
FROM       @Open o
INNER JOIN @Closed c on o.FeedbackID = c.FeedbackID and o.Indicator = c.Indicator
GROUP BY   o.FeedbackID

此时，您应该将TimeStamp差异封装在SUM()中，并按FeedbackID进行分组，用于FeedbackID有超过1个打开/关闭集的情况。

编辑:对仍未打开的案例进行核算

为了处理仍未打开的情况，我们首先需要将INNER JOIN更改为LEFT JOIN，这样就不会丢失缺少匹配的“关闭”记录的“打开”记录。

SELECT     SUM(DATEDIFF(day,o.TimeStamp,c.TimeStamp)) as "Days Open",
           o.FeedbackID
FROM       @Open o
LEFT JOIN  @Closed c on o.FeedbackID = c.FeedbackID and o.Indicator = c.Indicator
GROUP BY   o.FeedbackID

但是，这将在c.TimeStamp中填充仍未打开的情况下的null。为了处理这些问题，我们可以使用COALESCE()将null c.TimeStamp字段替换为更有意义的内容。由于这些情况仍然是开放的，我们最好使用GETDATE()并计算它们以这种方式打开的时间：

SELECT     SUM(DATEDIFF(day,o.TimeStamp,COALESCE(c.TimeStamp,GETDATE()))) as "Days Open",
           o.FeedbackID
FROM       @Open o
LEFT JOIN  @Closed c on o.FeedbackID = c.FeedbackID and o.Indicator = c.Indicator
GROUP BY   o.FeedbackID

Answer 2

这是我在亚伦的帮助下的最后一个答案，谢谢你的帮助:)

    DECLARE @TempTable TABLE (FeedbackID int, [Open/Closed] varchar(20), [TimeStamp] datetime)
    DECLARE @UIDTable TABLE (FeedBackID int, [UID] uniqueidentifier)
    DECLARE @Open Table (FeedbackID int, [Open/Closed] varchar(20), [TimeStamp] datetime, [Indicator] int)
    DECLARE @Closed Table (FeedbackID int, [Open/Closed] varchar(20), [TimeStamp] datetime, [Indicator] int)

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

INSERT INTO @TempTable (FeedbackID, [Open/Closed], [TimeStamp])
SELECT * FROM dbo.FeedbackChange
WHERE FeedbackID = FeedbackChange.FeedbackID
AND
[Open/Closed] IS NOT NULL
ORDER BY FeedbackID

INSERT INTO @UIDTable (FeedBackID, [UID])
SELECT FeedbackID, [UID] FROM tblFeedbackRequests fbr
where fbr.FeedbackID = FeedbackID

INSERT INTO @Open (FeedbackID, [Open/Closed], [TimeStamp],Indicator)
SELECT FeedbackID, [Open/Closed], TimeStamp, ROW_NUMBER() over (partition by FeedBackID order by TimeStamp asc) as [Indicator]
FROM @TempTable
Where [Open/Closed] = 'Open'

INSERT INTO @Closed (FeedbackID, [Open/Closed], [TimeStamp],Indicator)
SELECT FeedbackID, [Open/Closed], TimeStamp, ROW_NUMBER() over (partition by FeedBackID order by TimeStamp asc) as [Indicator]
FROM @TempTable
Where [Open/Closed] = 'Closed'

SELECT SUM(DATEDIFF(HOUR,o.TimeStamp,c.TimeStamp)) as "Days Open", o.FeedbackID
FROM @Open o 
INNER JOIN @Closed c on o.FeedbackID = c.FeedbackID and o.[Indicator] = c.Indicator
GROUP BY o.FeedbackID