InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 11:24:17 GMT20http://www.sqlservercentral.com/Forums/Topic1415922-392-1.aspxHi Jason-299789,Sorry for the late reply. I had to do another project in between and this had to wait.But, thank you very much for your answer.It works the way I want it with your query adjusted in this way:[code="sql"]WITH UserList_CTEAS( SELECT ROW_NUMBER() OVER (PARTITION BY AU.Useraccount_ID ORDER BY US.Useraccount_Status_DateTime) Rn , US.Useraccount_Status_DateTime , US.Useraccount_Status_Type_ID , AU.Useraccount_ID FROM Application_Useraccounts_Status AS US INNER JOIN Application_Useraccounts AS AU ON US.Useraccount_ID = AU.Useraccount_ID INNER JOIN Application_Useraccounts_Status_Types AS ST ON US.Useraccount_Status_Type_ID = ST.Useraccount_Status_Type_ID WHERE (US.Useraccount_Status_DateTime BETWEEN @beginDatum AND @eindDatum))Select AU.Useraccount_First_Name , AU.Useraccount_Last_Name , currul.Useraccount_Status_Type_ID , currul.Useraccount_Status_DateTime , Convert(time, futureul.Useraccount_Status_DateTime-currul.Useraccount_Status_DateTime) TijdsduurFrom Application_Useraccounts AU INNER JOIN UserList_CTE currul ON AU.Useraccount_ID=currul.Useraccount_ID LEFT JOIN UserList_CTE futureul on currul.Useraccount_ID=futureul.Useraccount_ID AND currul.Rn+1=futureul.rn[/code]Calculating the totals I will have to do in C# code (requirement of my boss).Now I can continue with this project.Greetz,GeertFri, 22 Mar 2013 06:29:44 GMTgeert.de.vylderhttp://www.sqlservercentral.com/Forums/Topic1415922-392-1.aspxNo problem, I wasnt sure which row you wanted the time assigned to, you could change the assignment round on the Lookup to the CTE a second time that Curr.Rn+1=Prev.Rn I would look at changing the alias of Prev to Future to show that you're looking forward rather than backward.For the Totals consider Dwains option of dropping them into a temp table then use a quiky update, to generate a running total, its a great concept and at first look it feels wierd, but when you understand it Running totals will never be the same...Wed, 06 Feb 2013 05:08:15 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1415922-392-1.aspxSorry,I meant Jason-299789 instead of Say Hey Kid. My apologies.Greetz,GeertWed, 06 Feb 2013 03:57:25 GMTgeert.de.vylderhttp://www.sqlservercentral.com/Forums/Topic1415922-392-1.aspxHi Jason-299789,Thank you very much for your reply and help.This query works with the only issue that the result TimeDifference is on the second row and should be on the first row.Now I also have to figure out to select also the row ST.Useraccount_Status_Type_Name. And how to calculate the total per status type per day.Greetz,GeertWed, 06 Feb 2013 03:08:40 GMTgeert.de.vylderhttp://www.sqlservercentral.com/Forums/Topic1415922-392-1.aspxIf your tables have many rows, performance is likely to be an issue so I'd recommend that you create a temp table to store the row vs. row time differences and populate that column using a Quirky Update.Here is a link to the seminal article on this topic by Jeff Moden:[url=http://www.sqlservercentral.com/articles/T-SQL/68467/]Solving the Running Total and Ordinal Rank Problems[/url]Tue, 05 Feb 2013 18:06:04 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1415922-392-1.aspxsomething like this should workI dont know how it will perform, on a large data set, but something like this is probably what you are looking for and should give you a good starting block.[code="sql"]WITH UserList_CTEAS( SELECT ROW_NUMBER() OVER (PARTITION BY AU.Useraccount_ID ORDER BY US.Useraccount_Status_DateTime) Rn , US.Useraccount_Status_DateTime , AU.Useraccount_ID FROM Application_Useraccounts_Status AS US INNER JOIN Application_Useraccounts AS AU ON US.Useraccount_ID = AU.Useraccount_ID INNER JOIN Application_Useraccounts_Status_Types AS ST ON US.Useraccount_Status_Type_ID = ST.Useraccount_Status_Type_ID WHERE (US.Useraccount_Status_DateTime BETWEEN @beginDatum AND @eindDatum))Select AU.Useraccount_First_Name , AU.Useraccount_Last_Name , currul.Useraccount_Status_DateTime , Convert(time, currul.Useraccount_Status_DateTime-prevul.Useraccount_Status_DateTime) TimeDifferenceFrom Application_Useraccounts AU INNER JOIN UserList_CTE currul ON AU.Useraccount_ID=currul.Useraccount_ID LEFT JOIN UserList_CTE prevul on currul.Useraccount_ID=prevul.Useraccount_ID AND currul.Rn-1=prevul.rn[/code]you can use dateDiff instead of the Time as the subtraction of the date times may cause an issue. You would need to lookup the additional data in main select to complete the query, as a final point you might want to consider moving the WHERE clause from the CTE to limit the data set, but testing should give you a good idea for the optimal on the where.Tue, 05 Feb 2013 09:35:34 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1415922-392-1.aspxHi everyone,I'm fairly new to T-SQL and I got stuck on a query.We have a large DB with a lot of tables. From those tables I have to use 3 tables in my query which is as follows:SELECT AU.Useraccount_First_Name, AU.Useraccount_Last_Name, ST.Useraccount_Status_Type_Name, US.Useraccount_Status_DateTime, US.Useraccount_Status_IDFROM Application_Useraccounts_Status AS US INNER JOIN Application_Useraccounts AS AU ON US.Useraccount_ID = AU.Useraccount_ID INNER JOIN Application_Useraccounts_Status_Types AS ST ON US.Useraccount_Status_Type_ID = ST.Useraccount_Status_Type_IDWHERE (US.Useraccount_Status_DateTime BETWEEN @beginDatum AND @eindDatum)ORDER BY AU.Useraccount_First_Name, AU.Useraccount_Last_Name, US.Useraccount_Status_DateTimeWhich gives me a result as followsMichel Hoekzema User Login 06-12-12 15:29:32 44989Michel Hoekzema Niet beschikbaar 06-12-12 15:29:35 44988Michel Hoekzema Niet beschikbaar 06-12-12 15:29:41 44987Michel Hoekzema User Logout 06-12-12 15:29:46 44986Michel Hoekzema User Login 07-12-12 09:54:29 44953Michel Hoekzema Niet beschikbaar 07-12-12 09:54:33 44952Michel Hoekzema Niet beschikbaar 07-12-12 09:54:40 44951etc...What I need now as result is the time difference in minutes (or seconds) between the 1st and 2nd row, the 2nd and 3rd row and so on.At the end I also need to calculate the total amount of minutes per status for the whole day and this by day.To get this result I need your help to adjust the query.In attachment you will find a text file to create the test tables and fill them with data.Thank you very much for your help and assistance.Greetz,GeertTue, 05 Feb 2013 09:02:40 GMTgeert.de.vylder