how to calculate duration spent on every visit

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » T-SQL » how to calculate duration spent on every...

how to calculate duration spent on every visit

Rate Topic

Display Mode

Topic Options

Author

Message

Shamshad Ali

Posted Sunday, December 30, 2007 11:16 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, January 12, 2012 10:21 PM
Points: 526, Visits: 588

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
SessionID StartDateTime EndDateTime
10212F68D7D6DBFE 2006-02-07 10:28:05.663 2006-02-07 11:29:13.293

Detail Table
SessionID PageId DateStamp DurationSpent (Seconds)
10212F68D7D6DBFE 10 2006-02-07 10:28:05.680 This time it calculate from master.startDateTime
10212F68D7D6DBFE 16 2006-02-07 10:28:26.523 This time it calculates from PageId (10)
DateStamp – PageId(16) dateStamp
10212F68D7D6DBFE 55 2006-02-07 10:28:32.540 This time it calculates from PageId (16)
DateStamp – PageId(55) dateStamp
10212F68D7D6DBFE 35 2006-02-07 10:28:54.790

Post #437427

GilaMonster

Posted Sunday, December 30, 2007 11:41 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:08 PM
Points: 38,099, Visits: 30,392

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

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #437431

Shamshad Ali

Posted Monday, December 31, 2007 12:04 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, January 12, 2012 10:21 PM
Points: 526, Visits: 588

sessionId pageid datetimestamp duration
10212F68D7D6DBFE 6 2006-02-07 10:28:05.680 21 sec
10212F68D7D6DBFE 8 2006-02-07 10:28:26.523 6 sec
10212F68D7D6DBFE 14 2006-02-07 10:28:32.540 22 sec
10212F68D7D6DBFE 6 2006-02-07 10:28:54.790 3619 sec

Post #437435

GilaMonster

Posted Monday, December 31, 2007 12:08 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:08 PM
Points: 38,099, Visits: 30,392

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

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #437437

vyas

Posted Monday, December 31, 2007 12:15 AM

Hall of Fame

Group: General Forum Members
Last Login: Thursday, May 16, 2013 4:02 AM
Points: 3,131, Visits: 1,056

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

Post #437442

Shamshad Ali

Posted Monday, December 31, 2007 12:16 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, January 12, 2012 10:21 PM
Points: 526, Visits: 588

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

Post #437443

Shamshad Ali

Posted Monday, December 31, 2007 12:22 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, January 12, 2012 10:21 PM
Points: 526, Visits: 588

sp_help tbl_tracking_mstr
====================

Session_Id varchar
Login_Id varchar
Start_DateTime datetime
End_DateTime datetime
IP_Address varchar

sp_help tbl_tracking_dtl
====================

Tracking_Dtl_Id numeric
Session_Id varchar
Page_Id numeric
DateStamp datetime

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

Post #437445

Shamshad Ali

Posted Monday, December 31, 2007 11:50 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, January 12, 2012 10:21 PM
Points: 526, Visits: 588

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.

Post #437697

Jeff Moden

Posted Tuesday, January 01, 2008 8:58 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 33,112, Visits: 27,038

-- 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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #437742

« Prev Topic | Next Topic »

Permissions