how to calculate duration spent on every visit

Question

how to calculate duration spent on every visit

Shamshad Ali

SSCarpal Tunnel

Points: 4822
More actions
December 30, 2007 at 11:16 pm

#62725

Following is table structure of (Master/detail) tables and I have to display durationSpent in seconds of a particular user session from detail table. Please help …
Master Table
SessionIDStartDateTimeEndDateTime
10212F68D7D6DBFE2006-02-07 10:28:05.6632006-02-07 11:29:13.293
Detail Table
SessionIDPageIdDateStampDurationSpent (Seconds)
10212F68D7D6DBFE102006-02-07 10:28:05.680 This time it calculate from master.startDateTime
10212F68D7D6DBFE162006-02-07 10:28:26.523 This time it calculates from PageId (10)
DateStamp – PageId(16) dateStamp
10212F68D7D6DBFE552006-02-07 10:28:32.540This time it calculates from PageId (16)
DateStamp – PageId(55) dateStamp
10212F68D7D6DBFE352006-02-07 10:28:54.790

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Can you give an example of the output you want please?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Shamshad Ali SSCarpal Tunnel Points: 4822 More actions · Answer 2

sessionIdpageiddatetimestampduration

10212F68D7D6DBFE62006-02-07 10:28:05.68021 sec

10212F68D7D6DBFE82006-02-07 10:28:26.5236 sec

10212F68D7D6DBFE142006-02-07 10:28:32.54022 sec

10212F68D7D6DBFE62006-02-07 10:28:54.790 3619 sec

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

How do those pageIDs link up with the ones you posted in your initial post?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Chandra Sekhara Vyas Dhara SSChampion Points: 10207 More actions · Answer 4

How do u relate master and detail table. What is the parameter other than session id?

Shamshad Ali SSCarpal Tunnel Points: 4822 More actions · Answer 5

select TrackingDtlid,

Session_Id, Page_Id, DateStamp, "duration-required" from tbl_tracking_dtl where session_id = '10212F68D7D6DBFE'

TrackingDtlid sessionId pageid datetimestamp duration

283808 10212F68D7D6DBFE 6 2006-02-07 10:28:05.680 21 sec

283812 10212F68D7D6DBFE 8 2006-02-07 10:28:26.523 6 sec

283815 10212F68D7D6DBFE 14 2006-02-07 10:28:32.540 22 sec

283816 10212F68D7D6DBFE 6 2006-02-07 10:28:54.790 3619 sec

I can do calculate via cursor but i don't want to use cursor to process one by one row, check if next row for same session exists then it may calculte duration from next row's datetimestamp otherwise if incase of last row it may calculate duration from master table's End_dateTime column.

Hope u understand and provide good solution.

Thanks

Shamshad Ali SSCarpal Tunnel Points: 4822 More actions · Answer 6

sp_help tbl_tracking_mstr

====================

Session_Idvarchar

Login_Idvarchar

Start_DateTimedatetime

End_DateTimedatetime

IP_Addressvarchar

sp_help tbl_tracking_dtl

====================

Tracking_Dtl_Idnumeric

Session_Idvarchar

Page_Idnumeric

DateStampdatetime

based on sessionId per tracking Page_Id visited, i have to calculate dateStamp difference from 1 Tracking Dtl ID to next page ID.

Shamshad Ali SSCarpal Tunnel Points: 4822 More actions · Answer 7

I have fixed my problem myself.

The problem was how to get next row if exists then calculate DateDiff (current Row's Time - Next row's time) till end of related rows. Incase of last row it calculates DateDiff from current rows's time - tracking master tables's session end time.

Hurray ......

select dtl.tracking_dtl_id, Login_Name, mstr.session_id, page_name, dtl.datestamp, isnull(dtl.SessionEndTime, mstr.end_datetime) as end_datetime,

dbo.formattime(datediff(s, dtl.datestamp, isnull(dtl.SessionEndTime, mstr.end_datetime))) as Duration

from tbl_tracking_mstr mstr

inner join

(select top 100 percent Tracking_Dtl_Id, Session_Id, Page_Id, DateStamp,

cnt = (select count(*) from tbl_tracking_dtl b where session_id = '99332BD543231F87C' and b.Tracking_Dtl_Id > a.Tracking_Dtl_Id

), SessionEndTime = (select top 1 c.DateStamp from tbl_tracking_dtl c where session_id = '99332BD543231F87C' and c.tracking_dtl_id > a.tracking_dtl_id order by tracking_dtl_id)

from tbl_tracking_dtl a where session_id = '99332BD543231F87C'

order by tracking_dtl_id ) dtl

on mstr.session_id = dtl.session_id

inner join tbl_tracking_webpages web

on dtl.page_id = web.page_id

where mstr.login_id = 'shamshad.ali'

and mstr.session_id = '99332BD543231F87C' and mstr.start_datetime between 'Jan 1 2006 12:00AM' and 'Jan 10 2006 11:59PM' and mstr.session_id = dtl.session_id

and dtl.page_id = web.page_id order by dtl.tracking_dtl_id

-- Plz close this post

Shamshad Ali.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 8

-- Plz close this post

First, thank you very much for posting your hard earned solution... that's what this forum is all about.

The only problem that I see with your solution is that it uses two correlated sub-queries and they each have, depending on the conditions and amount of your data, a performance sapping "Triangular Join" in them. See the following URL for more information on "Triangular Joins" and why the can be absolutely aweful for performance....

http://www.sqlservercentral.com/articles/T-SQL/61539/

Not that your solution is bad... it depends a lot on the data. Just telling you this so that if your solution slows down in the face of scalability, you know why it is. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)