Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to calculate duration spent on every visit Expand / Collapse
Author
Message
Posted Sunday, December 30, 2007 11:16 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, September 6, 2013 5:54 AM
Points: 526, Visits: 590
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
Posted Sunday, December 30, 2007 11:41 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:22 AM
Points: 40,632, Visits: 37,094
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
Posted Monday, December 31, 2007 12:04 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, September 6, 2013 5:54 AM
Points: 526, Visits: 590
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
Posted Monday, December 31, 2007 12:08 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:22 AM
Points: 40,632, Visits: 37,094
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
Posted Monday, December 31, 2007 12:15 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 7:16 AM
Points: 3,931, Visits: 1,102

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



Post #437442
Posted Monday, December 31, 2007 12:16 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, September 6, 2013 5:54 AM
Points: 526, Visits: 590
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
Posted Monday, December 31, 2007 12:22 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, September 6, 2013 5:54 AM
Points: 526, Visits: 590
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
Posted Monday, December 31, 2007 11:50 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, September 6, 2013 5:54 AM
Points: 526, Visits: 590
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
Posted Tuesday, January 1, 2008 8:58 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:16 PM
Points: 35,776, Visits: 32,447
-- 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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #437742
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse