Query help: Login sessions close to eachother

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)

Query help: Login sessions close to eachother

Author

Topic

Argyle
Yak Posting Veteran

53 Posts

Posted - 2003-01-06 : 07:53:23

Hi.

I need help with a query :)

I have a web site for a free online game. It hosts player info, statistics and other stuff for leagues in this game. The game has a "business rule" :) :
"It's not allowed for a player to be a member of and play for two teams (called squads) during a league."

So I plan to help out the admins of the game to track down possible "double squaders". To do that I need to write a query against my SessionLog table (where I track all logins) to find login sessions that are:

1. close to eachother (XX minutes, input parameter)
2. have same ip address
3. the username is different
4. are in the same league (input parameter)

Then I can get a list of possible doubles squadders and the admins can then check this Username/IP list against their game server logs to see if they actually played for two squads.

The thing is I don't know how to write the query :P

-----------------
Sessionlog Table:

CREATE TABLE [dbo].[SessionLog] (
	[sessionID] [char] (40) PRIMARY KEY  NONCLUSTERED ,
	[leagueID] [int] NOT NULL ,
	[userName] [varchar] (255) NOT NULL ,
	[userIP] [varchar] (15) NOT NULL ,
	[userHost] [varchar] (500) NOT NULL ,
	[userType] [char] (1) NOT NULL ,
	[userID] [int] NOT NULL ,
	[sessionState] [tinyint] NOT NULL ,
	[createdDate] [smalldatetime] NOT NULL ,
	[lastAccessedDate] [smalldatetime] NOT NULL 
) ON [PRIMARY]
GO

-----------------
Example data:

SELECT
	leagueID, userName, userIP, createdDate
FROM
	SessionLog
ORDER BY
	createdDate DESC

will give you this:
5	Damage		192.168.0.7	2002-12-12 22:03:00
5	Little		192.168.0.1	2002-12-12 22:03:00
5	Musta Tappaja	192.168.0.2	2002-12-12 22:01:00
5	Copy Guy	192.168.0.1	2002-12-12 22:01:00
5	Freedom B	192.168.0.4	2002-12-12 19:22:00
5	Nomad		192.168.0.8	2002-12-12 18:14:00
5	Polarizer	192.168.0.9	2002-12-12 17:50:00
5	bluecheese	192.168.0.9	2002-12-12 17:49:00
5	WannaPlay?!	192.168.0.9	2002-12-12 17:48:00
5	Zsl		192.168.0.3	2002-12-12 12:45:00
5	Brainbuster	192.168.0.5	2002-12-12 11:22:00
5	Johny		192.168.0.9	2002-12-12 11:01:00

-----------------

what I'm trying to do is write a query that from the above will return:

5	Little		192.168.0.1	2002-12-12 22:03:00
5	Copy Guy	192.168.0.1	2002-12-12 22:01:00
5	Polarizer	192.168.0.9	2002-12-12 17:50:00
5	bluecheese	192.168.0.9	2002-12-12 17:49:00
5	WannaPlay?!	192.168.0.9	2002-12-12 17:48:00

.
In other words a list of possible doublesquaders in the league with leagueID=5. The problem for me is that I don't know how to check if the row before and after another row is within XX minutes.

Any help would be appreciated :)

/Argyle
Edit: had some problems with the code tag

Edited by - argyle on 01/06/2003 07:57:11

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-01-06 : 08:23:53

something like the following should work....

select a.leagueID, a.userName, a.userIP, a.createdDate
FROM SessionLog a, SessionLog b
where a.leagueid = @inleagueparam
and abs(datediff(mi,a.createddate,b.createddate)) < @intimeparam
and a.userip = b.userip
and a.username <> b.username

Note...if your log is not purged between runs of this query, i would advise that you qualify the query with a "and createddate >= 'date/time of last run'" clause.....otherwise this function will take longer and longer with each successive run.

The performance of this won't be pretty...things might improve (somewhat) with some indices on the leagueid, userip and/or createddate columns.

Argyle
Yak Posting Veteran

53 Posts

Posted - 2003-01-06 : 09:40:34

Thx for the quick response :D . I think I get it now. That's a cross join right? Then you compare the time for one row with all other rows one at a time? Tested it and it works fine. I'll probably add some indexes to the table as you said.

Question about filtering in a cross join. Do I need to filter on both tables?

example:
where
a.createddate > @mydate
and b.createddate > @mydate

or is this enough:
where
a.createddate > @mydate

Then again, I could just test it out :P

/Argyle

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-01-06 : 11:11:47

it's a cross-join alright....(username <> username makes sure you don't compare a row to itself.)

the "where createdate >" clause probably needs to be on both tables....but as you've pointed out testing is best!!!

remember that the SQL cache needs to be cleared between runs....
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
so as to get independant (performance) results when testing.

before adding the indices, remember that there's an overhead in having them...ie slower insert/update...you might only need them once a month...in which case adding them just BEFORE you run this job and then dropping them right AFTERwards, might work out best for you......again....test

Enjoy!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources