abramhill abramhill - 6 months ago 17
SQL Question

Date Difference, T-SQL, multiple rows for ticketing system

I'm trying to get a stored procedure to calculate the amount of time a ticket was open, including multiple closes and opens. My data looks like this:

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


As you can see the FeedbackID #2146 has 4 events. I need to be able to calculate the difference between each Open and Closed event and then add them up if there are more than 2 Open and Closed Events. Any help is appreciated!

EDIT: Posting the code for the procedure below

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


END

Answer

First, make a temp table or CTE for open statuses, and another for closed statuses. Use ROW_NUMBER over (partition by FeedBackID order by TimeStamp asc) to create an indicator of which open status records match which closed records:

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'

Then JOIN these tables on FeedbackID and Indicator and do your date aggregation. This will allow us to be sure that the earliest open record matches up with the earliest closed record for each 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

At this point you should have your TimeStamp differences, wrapped in a SUM() and grouping by FeedbackID for the cases where a FeedbackID has more than 1 open/closed set.

Edit: Accounting for cases that are still open

To handle cases that are still open, we first need to change our INNER JOIN to a LEFT JOIN so we don't lose the 'open' records that lack a matching 'closed' record.

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

However, this will populate c.TimeStamp with null where cases are still open. To handle these, we can use COALESCE() to replace the null c.TimeStamp field with something more meaningful. Since these cases are still open, we might as well use GETDATE() and calculate how long they have been open that way:

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