InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 08:20:12 GMT20http://www.sqlservercentral.com/Forums/Topic437427-8-1.aspx[quote]-- Plz close this post[/quote]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....[url]http://www.sqlservercentral.com/articles/T-SQL/61539/[/url]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. ;)Tue, 01 Jan 2008 08:58:40 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic437427-8-1.aspxI 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 mstrinner 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 ) dtlon mstr.session_id = dtl.session_idinner join tbl_tracking_webpages webon dtl.page_id = web.page_idwhere 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 postShamshad Ali.Mon, 31 Dec 2007 23:50:32 GMTShamshad Alihttp://www.sqlservercentral.com/Forums/Topic437427-8-1.aspxsp_help tbl_tracking_mstr====================Session_Id varcharLogin_Id varcharStart_DateTime datetimeEnd_DateTime datetimeIP_Address varcharsp_help tbl_tracking_dtl ====================Tracking_Dtl_Id numericSession_Id varcharPage_Id numericDateStamp datetimebased on sessionId per tracking Page_Id visited, i have to calculate dateStamp difference from 1 Tracking Dtl ID to next page ID.Mon, 31 Dec 2007 00:22:43 GMTShamshad Alihttp://www.sqlservercentral.com/Forums/Topic437427-8-1.aspxselect TrackingDtlid, Session_Id, Page_Id, DateStamp, "duration-required" from tbl_tracking_dtl where session_id = '10212F68D7D6DBFE'TrackingDtlid sessionId pageid datetimestamp duration283808 10212F68D7D6DBFE 6 2006-02-07 10:28:05.680 21 sec283812 10212F68D7D6DBFE 8 2006-02-07 10:28:26.523 6 sec283815 10212F68D7D6DBFE 14 2006-02-07 10:28:32.540 22 sec283816 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.ThanksMon, 31 Dec 2007 00:16:34 GMTShamshad Alihttp://www.sqlservercentral.com/Forums/Topic437427-8-1.aspxHow do u relate master and detail table. What is the parameter other than session id?Mon, 31 Dec 2007 00:15:55 GMTvyashttp://www.sqlservercentral.com/Forums/Topic437427-8-1.aspxHow do those pageIDs link up with the ones you posted in your initial post?Mon, 31 Dec 2007 00:08:32 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic437427-8-1.aspxsessionId pageid datetimestamp duration10212F68D7D6DBFE 6 2006-02-07 10:28:05.680 21 sec10212F68D7D6DBFE 8 2006-02-07 10:28:26.523 6 sec10212F68D7D6DBFE 14 2006-02-07 10:28:32.540 22 sec10212F68D7D6DBFE 6 2006-02-07 10:28:54.790 3619 secMon, 31 Dec 2007 00:04:36 GMTShamshad Alihttp://www.sqlservercentral.com/Forums/Topic437427-8-1.aspxCan you give an example of the output you want please?Sun, 30 Dec 2007 23:41:06 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic437427-8-1.aspxFollowing 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 TableSessionID StartDateTime EndDateTime10212F68D7D6DBFE 2006-02-07 10:28:05.663 2006-02-07 11:29:13.293Detail TableSessionID PageId DateStamp DurationSpent (Seconds)10212F68D7D6DBFE 10 2006-02-07 10:28:05.680 This time it calculate from master.startDateTime10212F68D7D6DBFE 16 2006-02-07 10:28:26.523 This time it calculates from PageId (10)DateStamp – PageId(16) dateStamp10212F68D7D6DBFE 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 Sun, 30 Dec 2007 23:16:04 GMTShamshad Ali