Compute Session timings - 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 2005 Forums

Transact-SQL (2005)

Compute Session timings

Author

Topic

Dittakavi
Starting Member

3 Posts

Posted - 2009-03-29 : 23:42:11

HI,

I am looking for creation of procedure wherein i need to compute session timings. The scenario is as below:

We have couple users opening up mulitple session to connect to our applicaions. The session times may overlap ie. the user can open multiple sessions from the samebox. I need to generate a report for total effective time the user logged in. given below is an example:

user SessionStart time SessonEnd time
1 01/01/2009 10:00 AM 01/01/2009 11:30 AM
1 01/01/2009 10:15 AM 01/01/2009 12:00 PM
1 01/01/2009 1:00 PM 01/01/2009 3:00 PM
1 01/01/2009 1:30 PM 01/01/2009 2:20 PM
1 01/02/2009 10:00 AM 01/02/2009 4:00 PM
1 01/02/2009 2:00 PM 01/02/2009 3:00 PM

2 01/01/2009 11:00 AM 01/01/2009 11:30 AM
2 01/01/2009 11:15 AM 01/01/2009 12:00 PM
2 01/01/2009 1:00 PM 01/01/2009 2:00 PM
2 01/01/2009 1:30 PM 01/01/2009 2:20 PM
2 01/02/2009 10:00 AM 01/02/2009 4:00 PM
2 01/02/2009 2:00 PM 01/02/2009 3:00 PM

so on..

The report that we are looking towards is, each time the uesr logs in, what is the effective time the session was open. If there are any overlapping sessions then the start time of first session and the end time of the last session closed must be taken to compute the duriation.

in the above exmple for user 1, the duraiton for first 2 records = 10:00 AM - 12:00 AM = 120 mins, 1:00 PM - 3:00 PM = 120 Mins as the other session on first are between these sessions.

Is there a simple way this can be achieved.

Nitin

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-03-30 : 00:16:10

Use Min and max function

Select DATEDIFF ( mi, min(sessionstarttime), max(sessionstarttime)) from table group by userid

Regards

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

Dittakavi
Starting Member

3 Posts

Posted - 2009-03-30 : 00:57:47

Hi,

This gives me the differene between the min and max session timings not the duration for which the session was used.
Ex: if i have opened the session 4 times a day say at 10, 11, 3 and 5 and closed them at 12, 11:30, 4 and 5:15 respectively, the above quey will only fetch the difference of time between 10 and 5:15 but not compute the intervals of time when my sesison was active.

We need to compute the session time the user has been logged on for the billing and cannot go flat on the min and max time.. it has to be based on the session start and end times and must ensure that there is no double recording of time to avoid double billing

Nitin

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-03-30 : 05:36:26

I can't understand ur need.. try this it may be urs..

select userid,datediff(mi,sessionstarttime,sessionendtime)as sess into #temp
from table

select sum(sess),userid from #temp group by userid

Regards

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

Dittakavi
Starting Member

3 Posts

Posted - 2009-03-30 : 21:39:15

The scenario is a bit different that what the above code is for. Its not just for the duration of session that i am looking for. For example in the above example if we compute the duration of the user session for user 1 :

user SessionStart time SessonEnd time Duration
1 01/01/2009 10:00 AM 01/01/2009 11:30 AM 90
1 01/01/2009 10:15 AM 01/01/2009 12:00 PM 105
1 01/01/2009 1:00 PM 01/01/2009 3:00 PM 120
1 01/01/2009 1:30 PM 01/01/2009 2:20 PM 50
1 01/02/2009 10:00 AM 01/02/2009 4:00 PM 360
1 01/02/2009 2:00 PM 01/02/2009 3:00 PM 60

Taking the above example, the user had effective session as follows :
user SessionStart time SessonEnd time Duration
1 01/01/2009 10:00 AM 01/01/2009 12:00 AM 120
1 01/01/2009 1:00 PM 01/01/2009 3:00 PM 120
1 01/02/2009 10:00 AM 01/02/2009 4:00 PM 360
All the other session are overlapping or between the effective times. I needed the procedure to fetch these details.

I got the results required via a proc posted by Brad and Aaron. Copying the porc from Brad for reference which achieves the desired result:

declare @t table (userid int, sttime datetime, entime datetime)

insert into @t

select 1, '2009-01-01 10:00 AM' ,'2009-01-01 11:30 AM' union all

select 1, '2009-01-01 10:15 AM' ,'2009-01-01 12:00 PM' union all

select 1, '2009-01-01 1:00 PM' ,'2009-01-01 3:00 PM' union all

select 1, '2009-01-01 1:30 PM' ,'2009-01-01 2:20 PM' union all

select 1, '2009-01-02 10:00 AM' ,'2009-01-02 4:00 PM' union all

select 1, '2009-01-02 2:00 PM' ,'2009-01-02 3:00 PM' union all

select 2, '2009-01-01 11:00 AM' ,'2009-01-01 11:30 AM' union all

select 2, '2009-01-01 11:15 AM' ,'2009-01-01 12:00 PM' union all

select 2, '2009-01-01 1:00 PM' ,'2009-01-01 2:00 PM' union all

select 2, '2009-01-01 1:30 PM' ,'2009-01-01 2:20 PM' union all

select 2, '2009-01-02 10:00 AM' ,'2009-01-02 4:00 PM' union all

select 2, '2009-01-02 2:00 PM' ,'2009-01-02 3:00 PM'

;with starttimes as

(

select distinct userid, sttime as s

from @t as o

where not exists (select 1 from @t as i

where i.userid=o.userid

and o.sttime>i.sttime

and o.sttime<=i.entime)

)

,endtimes as

(

select distinct userid, entime as e

from @t as o

where not exists (select 1 from @t as i

where i.userid=o.userid

and o.entime>=i.sttime

and o.entime<i.entime)

)

,sessiongroups as

(

select userid

,startsession=s

,endsession=(select min(e)

from endtimes et

where et.userid=st.userid

and e>=s)

from starttimes st

)

select userid

,startsession

,endsession

,numminutes=datediff(minute,startsession,endsession)

from sessiongroups

/*

userid startsession endsession numminutes

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

1 2009-01-01 10:00:00.000 2009-01-01 12:00:00.000 120

1 2009-01-01 13:00:00.000 2009-01-01 15:00:00.000 120

1 2009-01-02 10:00:00.000 2009-01-02 16:00:00.000 360

2 2009-01-01 11:00:00.000 2009-01-01 12:00:00.000 60

2 2009-01-01 13:00:00.000 2009-01-01 14:20:00.000 80

2 2009-01-02 10:00:00.000 2009-01-02 16:00:00.000 360

*/

Thanks again for looking into this..

Nitin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources