Finding lines in use 1

[gbaughma]

I'm faced with a scenario that I need to write a report for.
I have phone logs.... they look similar to this:
Line Start Time End Time
trunk1 12:00:01 12:15:15
trunk2 12:10:00 12:20:00
trunk3 12:18:00 12:30:00

(that's the basics)
What I need is a graph, showing lines in use. Not just how many minutes each line was in use (that would be easy), but something like:

3 lines in use XXX
2 lines in use XXXXXX
1 line in use XXXXXXXXXXXXXXXXX

.... so that you could see, at a glance, how long BOTH lines were in use.

I hope I'm making sense.... essentially, we need to show how many minutes on a week, month, etc. that ALL lines were in use, or 3 lines, or 2 lines, or 1 line.

The only way I can think to do this is to loop minute by minute, and take a count of how many lines were in use at that minute, and tally it all up. Needless to say, a loop this size would be very costly.

What do you folks think?

TIA!


Just my 2ó

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

 

[SkipVought]

Hi,

I don't understand BOTH when there are more than two.

How many lines are there?

 

[gbaughma]

The number of lines can vary.
Some centers have two, some have 3, some have 4, some have 6....

It's for dispatchers... I essentially need to know "How often were ALL lines busy (indicating people possibly getting busy signals) for a date range?"

The output would be something like this (assuming they have 4 lines)

4 Lines in use XXXXXX
3 Lines in use XXXXXXXXXXXXXX
2 Lines in use XXXXXXXXXXXXXXXXXXXXXXXXXX
1 line in use XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

.... in, say, minutes for the date range. So, for 6 minutes yesterday, all 4 lines were in use.... for 12 minutes, 3 lines were in use.

The table I have has a line name (which I really don't care about), a "Start Time" and an "End Time"
Line StartTimeStamp DisconnectTimeStamp
1 01/01/2000 1:00:00 01/01/2000 1:15:00
2 01/01/2000 1:10:30 01/01/2000 1:17:00

Does that make more sense?


Just my 2ó

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

 

[Olaf Doschke]

I don't see a graphical representation of the minutes helpful. The graph should rather be about a percentage of the time span you queried overall.
I could also imagine a sheet with time axis showing lines from start to end datetimes. For any given point on the time axis you then could determine the number of phone lines being drawn as active.

Despite of that, how to determine number of lines used should be quite simple:

Select StarttimeStamp as Timestamp, 1 as change from yourtable
Union All
Select DisconnectTimeStamp as Timestamp, -1 as change from yourtable

Ordering that by Timestamp the sum of all changes up to a certain timestamp will give the number of lines in use at that time until the next timestamp. So the number of lines used is valid between two timestamps, determining the diff in seconds and summing that up for each number of lines used will give you the final result.

--sample data (put in your table instead of @yourtable later)
Declare @yourtable as Table (StartTimeStamp datetime,  DisconnectTimeStamp datetime);
Insert into @yourtable values 
('12:00:01','12:15:15'), ('12:10:00', '12:20:00'), ('12:18:00','12:30:00');

-- parameters for the final query, the time span for your statistic
Declare @t1 as datetime,@t2 as datetime;
set @t1='12:00:30';
set @t2='12:19:00';

With occupancychanges (ts, change) as
(Select StartTimeStamp as ts, 1 as change from @yourtable
Union All
Select DisconnectTimeStamp as ts, -1 as change from @yourtable)

Select occupiedlines_timespans.occupiedcount, 
Cast(Sum(datediff(second,
Case When StartTimeStamp>@t1 Then StartTimeStamp else @t1 end, 
Case When EndTimeStamp<@t2 Then EndTimeStamp else @t2 end))/60.0 as real) as TotalMinutes
From
(SELECT
	SUM(change) OVER (ORDER BY ts ROWS UNBOUNDED PRECEDING) as occupiedcount,
	ts as StartTimeStamp,
	LEAD(ts) OVER (ORDER BY ts) as EndTimeStamp
FROM occupancychanges) occupiedlines_timespans
Where @t1<EndTimeStamp AND StartTimeStamp<@t2
Group by occupiedlines_timespans.occupiedcount

This is making use of newer features, windowing functions like LEAD and ROWS.

Bye, Olaf.

 

[gbaughma]

That looks brilliant.... one question... where would I put a filter in? I need to filter on a field called "QueueName"... trying to figure out your logic, just wondering where I would put "QueueName = 'admin'", for example.



Just my 2ó

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

 

[Olaf Doschke]

Have you read the comments?

QueueName must be a field in your original data, so you put it into the query querying your table (@yourtable).

So you'd need to put it into this section:

With occupancychanges (ts, change) as
(Select StartTimeStamp as ts, 1 as change from @yourtable [COLOR=#CC0000]where QueueName=@queuename[/color]
Union All
Select DisconnectTimeStamp as ts, -1 as change from @yourtable [COLOR=#CC0000]where QueueName=@queuename[/color])

Bye, Olaf.

 

[gbaughma]

Absolutely brilliant. Thank you so much. So much better than using a tally table or bob forbid a loop....


Just my 2ó

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

 

[Olaf Doschke]

You could also limit the data you union by adding where clauses for @t1 and @t2. The tricky thing is, you need the lowest StartTimeStamp just lower than @t1 and the highest DisconnectTimeStamp just higher than @t2.

Bye, Olaf.

 

[gbaughma]

While I understand that I might miss one call that started at 11:59 the day before my report, the overall data is great, and will make my bar graph very nice and useful. Now if I could only wrap my brain around what you did, specifically. lol... time to go back to school. I got the union, I just don't understand the OVER, UNBOUNDED, etc.... but it demonstrates some amazing code, and shows that I always have something new to learn. Thank you so much again!



Just my 2ó

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

 

[Olaf Doschke]

Just for the protocol, this is where I got my inspiration about using ROWS UNBONDED PRECEDING instead of Tally table:

http://sqlperformance.com/2012/07/t-sql-queries/running-totals

To find it I searched for running sums, as we need a running sum of the changes to get occupiedcount to finally group by that and sum the durations.

Bye, Olaf.