Set based solution needed - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Set based solution needed

Author

Topic

SMerrill
Posting Yak Master

206 Posts

Posted - 2003-08-29 : 14:59:48

I need to know how to architect a set-based solution for this.
The input is Agent Login/Logout events, and the output should be the number of agents logged in at any given time.

The output will be grouped by one statistic every half-hour, but don't concentrate on that requirement as much.

The input looks like this. An agent logs in, and out with a LOGIN record, but may take breaks during the day with a WALKAWAY record.
I hope this makes sense.


AGENT_ID    PERIOD   Date         START_TIME END_TIME 
----------- -------- ------------ ---------- -------- 
4141        LOGIN    01/01/2003   09:05:30   17:59:31
4141        WALKAWAY 01/01/2003   10:11:06   10:11:58
4141        LOGIN    01/03/2003   10:10:08   14:38:59
4141        LOGIN    01/03/2003   15:32:34   18:00:57
4141        LOGIN    01/04/2003   09:55:52   18:00:40
4141        LOGIN    01/05/2003   09:59:58   18:01:04
4141        LOGIN    01/06/2003   13:02:15   17:01:00
4141        LOGIN    01/07/2003   10:00:41   20:00:40
4141        LOGIN    01/08/2003   10:02:09   20:00:34
4141        WALKAWAY 01/08/2003   19:08:03   19:17:49
4141        LOGIN    01/09/2003   10:00:06   20:00:28
4141        WALKAWAY 01/09/2003   10:22:13   10:22:43
4141        LOGIN    01/10/2003   09:58:03   17:59:09
4141        LOGIN    01/11/2003   09:59:56   18:00:09
4141        LOGIN    01/12/2003   09:52:00   10:02:56
4141        LOGIN    01/12/2003   10:03:12   17:59:35
4141        LOGIN    01/13/2003   12:55:39   16:59:12
4141        LOGIN    01/14/2003   10:00:54   20:00:47
4141        WALKAWAY 01/14/2003   11:21:34   11:40:06
4141        LOGIN    01/15/2003   10:00:24   19:59:50
4141        WALKAWAY 01/15/2003   12:25:35   12:25:43
4141        WALKAWAY 01/15/2003   17:02:14   17:04:26
4141        WALKAWAY 01/15/2003   17:30:51   17:31:05
4141        WALKAWAY 01/15/2003   19:01:51   19:03:17
4141        LOGIN    01/16/2003   10:01:53   19:56:39
4141        WALKAWAY 01/16/2003   15:36:57   15:40:45
4141        WALKAWAY 01/16/2003   15:51:46   15:55:36
4141        WALKAWAY 01/16/2003   16:01:32   16:02:10
4141        WALKAWAY 01/16/2003   16:42:55   16:46:05

I'm working hard on this, but thanks for your thoughts if anyone has done this before.

~ Shaun Merrill
Seattle, WA

SamC
White Water Yakist

3467 Posts

Posted - 2003-08-29 : 16:03:23

I'm not clear on calculating the number of Admins logged in the current half hour.

Every login record in your sample data has an end_time.

How do I know when a User is currently Logged in? Is the END_TIME NULL?

Sam

Next, does walkaway count as a logged in user or a logout user? What's the meaning of the end time in the walkaway record?

SMerrill
Posting Yak Master

206 Posts

Posted - 2003-08-29 : 16:19:37

This whole idea centers around the business question, "How many average and maximum simultaneous logins do we have over time, to the granularity of 30-minute intervals?"
This information should feed a graph of Time vs. Login count.

4141        LOGIN    01/09/2003   10:00:06   20:00:28
4141        WALKAWAY 01/09/2003   10:22:13   10:22:43

On January 9th, Agent #4141 logged in at 10:00:06. He walked away from his post at 10:22:13 and returned to his post at 10:22:43. He logged out and went home at 20:00:28.
The records shown above are for only one agent...we have hundreds of different agents.

~ Shaun Merrill
Seattle, WA

SMerrill
Posting Yak Master

206 Posts

Posted - 2003-08-29 : 16:27:39

A more formal answer:

Login Start_Time is the time he logs in, and
Login End_Time is when he logs out.
A Walkaway Start_Time is the time he logged out, and
a Walkaway End_Time is the time he logged in.

Zero or many Walkaway records are possible for an agent in a given day.

~ Shaun Merrill
Seattle, WA

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-08-29 : 16:32:15

I've done something like this before. What you have to do is create a table (temp or regular) that has one record for each half hour (or possibly one record per minute and then group by half hour.

Then, you join your imput data to that table and count the number of rows that fall between each data point.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

nr
SQLTeam MVY

12543 Posts

Posted - 2003-08-29 : 17:05:14

Do you want the agents who were on-line at some time during the half hour?
Create the temp table as suggested by Michael and

select tmp.tm_start, tml.tm_end, count(distinct AGENT_ID)
from tmp, tbl
where tbl.PERIOD = LOGIN
and tbl.START_TIME < tmp.tm_start
and tbl.END_TIME > tmp.tm_end

to include the walkaway recs

select tmp.tm_start, tml.tm_end, count(distinct AGENT_ID)
from tmp, tbl
where tbl.PERIOD = LOGIN
and tbl.START_TIME <= tmp.tm_start
and tbl.END_TIME >= tmp.tm_end
and not exists (select * from tbl t2 where t2.AGENT_ID = tbl.AGENT_ID and t2.PERIOD = WALKAWAY and t2.START_TIME <= tmp.tm_start and t2.END_TIME >= tmp.tm_end)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SMerrill
Posting Yak Master

206 Posts

Posted - 2003-08-29 : 17:08:12

OK, I've just now gotten that far. Now I have a mid-level query that gives me the number of seconds that an agent has logged in during any half-hour interval, which contains 1,800 seconds. (I'm pretty proud of myself for getting that far.)


Agent Interval       Tot_Staffed_Time
----- --------       ----------------
101   1/1/2003 00:00 1800
101   1/1/2003 00:30 1800
102   1/1/2003 00:00  900
102   1/1/2003 00:30 1800
101   1/2/2003 04:00 1800
101   1/2/2003 04:30 1800
102   1/2/2003 04:00  895
102   1/2/2003 04:30 1800

If there is no record for an agent during that interval, he was not logged in at all during that interval.

Now, I have narrowed this down to a STATISTICS QUESTION:

If two agents logged in during a half-hour, I would have to consider that they must have been simultaneously logged in if they had logged in for more than half that interval (600 seconds). But if I had ten agents, in order to say they must have been simultaneous, I would be able to narrow that down to 180 seconds per agent. Any shorter of a window would not guarantee that they were simultaneous.

So I am wondering if I should make this window a variable, or make it fluctuate with each interval as shown above. Making it a variable would fix it at only one value, which may be statistically incorrect.

Any help with this one

~ Shaun Merrill
Seattle, WA

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-08-30 : 08:13:52

I think that, because of the 1-second accuracy of the LOG TIMES, you
should process each of 86400 seconds within each 24 hours of each day.
Below I assume that the LOG TIMES are represented as INT numbers,
i.e., 02:10:15 is equal to 7815 (# of the first second from 00:00:00).

declare @d int, @i int, @j int, @k int, @n int, @maxn int
set @d=0
while @d<365
set @d=@d+1 set @i=0 set @j=0
begin
while @i<48 --- 48 = 2 * 24 hours
begin
set @maxn=0
while @j<1800
begin
set @k=@i*1800+@j --- # of current second
select @n=sum(
case when period='login' and @k>=start_time and @k<end_time then 1
else
case when period='walkaway' and @k>=start_time and @k<end_time
then -1 else 0 end
end
) from t where datepart(dd,myDate)=@d
if @n>@maxn set @maxn=@n
set @j=@j+1
end
insert into #temp select @d, @i, @maxn
set @i=@i+1
end
end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources