SQLTeam.com | Weblogs | Forums

SQL Query: Report "Time in Queue (Days)"


#1

I'm looking for some help creating a SQL Query where I have the following data

Queue.IssueNumber, Queue.EntryDate and Queue.Activity

Each time a record comes into the Queue, a record is written to the Queue table

IssueNumber = 1
EntryDate = 9/01/2015
Activity = Into Queue #1

Each time an employee moves the record out of the queue, a record is written in the Queue table

IssueNumber = 1
EntryDate = 9/15/2015
Activity = Exit Queue #1

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.

Any help is greatly appreciated !!


#2

I'll want the output to display 1 record for each distinct issue

      Issue Number  |  Time in Queue (Days) | Completion Date

Completion date would be when Activity = Into Queue #2.


#3
SELECT IssueNumber, DATEDIFF(DAY, MIN(EntryDate ), MAX(EntryDate )), MAX(EntryDate)
FROM   Queue
GROUP BY IssueNumber

#4

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).


#5

can you post all possible type of activity ?

is there an Activity Type in the table ?

the activity is always in pair (IN and OUT) ?

also, i assumed there will be multiple entry of "In Queue (again)" and "Out Queue (again)" ?


#6

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".


#7

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


#8

Its a string but for simplicity sake lets say

1 = In
2 = Out

It does not know in (again) and out (again) so they too would also just display as

1 = In
2 = Out
1 = In
2 = Out


#9
;

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

#10

I lied ... I'm not able to convert this to like '%Added to Queue 1%' like I originally thought :frowning:


#11

LOL. What is the format / sample value of the "Activity" like ?


#12

added to the 1: PS-EAS Requests (Cube)
removed from the 1: PS-EAS Requests (Cube)


#13
CASE WHEN Activity LIKE 'added%' then 1
     WHEN Activity LIKE 'removed%' then 2
     END AS In_Out

#14

I'm sorry - where does that go within the larger CTE


#15
;   
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

#16

You are my HERO !! Thank you soooo much ... I have a lot to learn :slight_smile: