Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


how to calculate duration spent on every visit


how to calculate duration spent on every visit

Author
Message
Shamshad Ali
Shamshad Ali
Mr or Mrs. 500
Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)

Group: General Forum Members
Points: 554 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



GilaMonster
GilaMonster
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54774 Visits: 44670
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
Shamshad Ali
Mr or Mrs. 500
Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)

Group: General Forum Members
Points: 554 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



GilaMonster
GilaMonster
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54774 Visits: 44670
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
Chandra Sekhara Vyas Dhara
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4005 Visits: 1149
How do u relate master and detail table. What is the parameter other than session id?



Shamshad Ali
Shamshad Ali
Mr or Mrs. 500
Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)

Group: General Forum Members
Points: 554 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



Shamshad Ali
Shamshad Ali
Mr or Mrs. 500
Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)

Group: General Forum Members
Points: 554 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.



Shamshad Ali
Shamshad Ali
Mr or Mrs. 500
Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)

Group: General Forum Members
Points: 554 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.



Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52053 Visits: 40318
-- 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. Wink

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search