My goal is to calculate the time (Days) the issue has been in the queue ... Its easy to see visually that 9/1 was the entry date and 9/15 was the exit date where it was in the queue for 14 days. My question is how do i go through a table with 500 records and 150+ issue numbers AND some issues enter and exit the queue multiple times.
That's a great start and gets me 80% of the way there but I think I'm looking for something that goes one level further. That will work when there is ONLY two dates in the queue for the issue (one in and one out). Sometimes the following happens
In Queue 9/1
Out Queue 9/15
In Queue (again) 9/25
Out Queue (again) 9/26
In that scenario I would want to capture 9/15 - 9/1 = 14 and 9/25 - 9/26 = 1 for a total of 15 days in the queue. Not 9/26 - 9/1 which would be 25 days ... The goal is to get a summation of only the days it's in the queue. It would be great if we could get to "N" times this happens but for now I see the max in our data set as 4 (ins and outs).
The Activity is a system generated string and they are listed below: :
#1) added to the 1: PS-EAS Requests #2) removed from the 1: PS-EAS Requests
Activity wont always be in pairs ... Any non-pairs signal that its still in the queue.
In (still there when report runs)
In --> Out (No longer in queue)
In --> Out --> In (still in queue when report runs)
In --> Out --> In --> Out (No longer in queue)
In --> Out --> In --> Out --> [can repeat 'n' times in theory]
For those that dont have a matching out, we can use the current date to perform the calculation for "Days in queue".
so how does this IN , OUT status store in the table ? Hopefully not like "Into Queue #1", "Exit Queue #1". That will required some string parsing effort
;
WITH cte
AS (
SELECT *,
rn = row_number() OVER (
PARTITION BY IssueNumber ORDER BY EntryDate,
Activity
),
rn2 = row_number() OVER (
PARTITION BY IssueNumber,
Activity ORDER BY EntryDate DESC
)
FROM QUEUE
)
SELECT i.IssueNumber,
days = sum(datediff(day, i.EntryDate, isnull(o.EntryDate, getdate()))),
complete = max(CASE
WHEN i.rn2 = 1
THEN o.EntryDate
END)
FROM cte i
LEFT JOIN cte o
ON i.IssueNumber = o.IssueNumber
AND i.rn = o.rn - 1
WHERE i.Activity = 1
GROUP BY i.IssueNumber
;
WITH Q
AS (
SELECT *,
In_Out = CASE
WHEN Activity LIKE 'added%'
THEN 1
WHEN Activity LIKE 'removed%'
THEN 2
END
FROM QUEUE
),
cte
AS (
SELECT *,
rn = row_number() OVER (
PARTITION BY IssueNumber ORDER BY EntryDate,
In_Out
),
rn2 = row_number() OVER (
PARTITION BY IssueNumber,
In_Out ORDER BY EntryDate DESC
)
FROM Q
)
SELECT i.IssueNumber,
days = sum(datediff(day, i.EntryDate, isnull(o.EntryDate, getdate()))),
complete = max(CASE
WHEN i.rn2 = 1
THEN o.EntryDate
END)
FROM cte i
LEFT JOIN cte o
ON i.IssueNumber = o.IssueNumber
AND i.rn = o.rn - 1
WHERE i.In_Out = 1
GROUP BY i.IssueNumber
