SOLVED ---- No duplicate login count

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

General SQL Server Forums

New to SQL Server Programming

SOLVED ---- No duplicate login count

Author

Topic

qube
Starting Member

3 Posts

Posted - 2010-11-30 : 07:17:05

Hi everyone,

I have this query that counts the number of sessions per instant:
----------------------------------------------------------------------
SELECT TOP (100) PERCENT COUNT(*) AS SessionNumber, T.instant
FROM dbo.COMPLETE_SESSIONS AS S INNER JOIN
dbo.timesComplete AS T ON S.openDate < T.instant AND T.instant < S.closeDate
GROUP BY T.instant
ORDER BY T.instant
----------------------------------------------------------------------

COMPLETE_SESSIONS table contains details about user sessions in three columns : login, opendate and closedate (expressed as datetime : yyyy-mm-dd hh.ss)
timesComplete table contains only one column : instant (express as datetime yyyy-mm-dd hh.ss).

The query showed above works but I need a way to edit it to count only one session per login.

I'll try to explain my issue with an example:
For example if COMPLETE_SESSIONS table contains this data :

|Login|-------|----OpenDate---|---|---CloseDate---|
qube ---------2010/11/30 16.29----2010/11/30 16.34
qube ---------2010/11/30 16.32----2010/11/30 16.39
qube ---------2010/11/30 16.33----2010/11/30 18.38
cone ---------2010/11/30 16.33----2010/11/30 16.36
qube ---------2010/11/30 16.34----2010/11/30 16.37
qube ---------2010/11/30 16.36----2010/11/30 16.38
......
--------------------------------------------------------------
for T.instant = 2010/11/30 16.35, my query result is 4. But I need a query that counts only one session for qube login in 2010/11/30 16.35 instant. So the right result should be 2.

Thanks in advance,
Q

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-11-30 : 07:52:51

How can you tell which records belong to a session? To my understanding this is a matter of ignoring "duplicate" entries and to do this we need something that will identify one session from another. The records for the "qube" login all seem to belong to the same session... Can you provide some working sample tables and your expected output?

declare @complete_sessions table(...) 
insert into @complete_sessions ...

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

qube
Starting Member

3 Posts

Posted - 2010-11-30 : 09:07:55

Lumbago, thanks for your reply.

You're right, I didn't tell you that every Session has a SID. These sessions are identified as different sessions. I have this table COMPLETE_SESSIONS that contains all the separated sessions that users create when they log on a certain application. Users can create more than a session per instant but I need to count only one session per instant per login. So if there are 3 records for a login in a certain instant , my query has to count only 1 record for that login in that instant. I don't know if I was able to explain better my issue.

However here it is one of my working sample table.

COMPLETE_SESSSIONS:
SID--------------login------openDate-----------closeDate-------
--------------------------------------------------------------
1702811166------qube-------2010/11/08 02.28---2010/11/08 02.33
536535635-------qube-------2010/11/08 02.29---2010/11/08 02.33
880143124-------cone-------2010/11/08 02.30---2010/11/08 02.36
2138885892------pyramid----2010/11/08 02.31---2010/11/08 02.48
1107428660------sphere-----2010/11/08 02.32---2010/11/08 02.35
1296559701------cone-------2010/11/08 02.34---2010/11/08 02.36
1352582015------cone-------2010/11/08 02.36---2010/11/08 02.38
1084126320------qube-------2010/11/08 02.36---2010/11/08 02.40
1991183239------qube-------2010/11/08 02.37---2010/11/08 02.54

............

And then timesComplete table:

Instant------------|
2010/11/08 02.32
2010/11/08 02.33
2010/11/08 02.34
2010/11/08 02.35
2010/11/08 02.36
2010/11/08 02.37
2010/11/08 02.38

According to the condition:
S.openDate < T.instant AND T.instant < S.closeDate
the output I expect is a table like this:

SessionNumber----Instant---------
--------------------------------
3----------------2010/11/08 02.32
3----------------2010/11/08 02.33
3----------------2010/11/08 02.34
1----------------2010/11/08 02.35
0----------------2010/11/08 02.36
1----------------2010/11/08 02.37
1----------------2010/11/08 02.38

thanks again
Q

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-11-30 : 09:19:12

Ah now I get it...I think hehe. Hold on, I'll try to make something.

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-11-30 : 09:21:45

Ok, I think this was easier than I expected...is this what you need:

SELECT COUNT(DISTINCT login) AS SessionNumber, T.instant
FROM dbo.COMPLETE_SESSIONS AS S 
	INNER JOIN dbo.timesComplete AS T 
		ON S.openDate < T.instant AND T.instant < S.closeDate
GROUP BY T.instant
ORDER BY T.instant

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

qube
Starting Member

3 Posts

Posted - 2010-11-30 : 09:39:20

Lumbago thanks very much! I tried to use your query and it works!
Thank you again.
Q

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-11-30 : 09:49:17

So the solution was actually very easy. Understanding the problem however...a lot harder!

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources