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

Calculate time difference between multiple rows Expand / Collapse
Author
Message
Posted Tuesday, February 5, 2013 9:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 7:47 AM
Points: 24, Visits: 141
Hi 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_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)
ORDER BY AU.Useraccount_First_Name, AU.Useraccount_Last_Name, US.Useraccount_Status_DateTime

Which gives me a result as follows

Michel Hoekzema User Login 06-12-12 15:29:32 44989
Michel Hoekzema Niet beschikbaar 06-12-12 15:29:35 44988
Michel Hoekzema Niet beschikbaar 06-12-12 15:29:41 44987
Michel Hoekzema User Logout 06-12-12 15:29:46 44986
Michel Hoekzema User Login 07-12-12 09:54:29 44953
Michel Hoekzema Niet beschikbaar 07-12-12 09:54:33 44952
Michel Hoekzema Niet beschikbaar 07-12-12 09:54:40 44951
etc...

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,

Geert


  Post Attachments 
TestTable.txt (6 views, 574.10 KB)
Post #1415922
Posted Tuesday, February 5, 2013 9:35 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
something like this should work

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

WITH UserList_CTE
AS(
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) TimeDifference
From
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

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.



_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1415952
Posted Tuesday, February 5, 2013 6:06 PM


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: 2 days ago @ 4:14 AM
Points: 3,618, Visits: 5,254
If 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:
Solving the Running Total and Ordinal Rank Problems



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1416187
Posted Wednesday, February 6, 2013 3:08 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 7:47 AM
Points: 24, Visits: 141
Hi 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,

Geert
Post #1416356
Posted Wednesday, February 6, 2013 3:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 7:47 AM
Points: 24, Visits: 141
Sorry,

I meant Jason-299789 instead of Say Hey Kid. My apologies.

Greetz,

Geert
Post #1416384
Posted Wednesday, February 6, 2013 5:08 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
No 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...


_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1416418
Posted Friday, March 22, 2013 6:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 7:47 AM
Points: 24, Visits: 141
Hi 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:

WITH UserList_CTE
AS(
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) Tijdsduur
From
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

Calculating the totals I will have to do in C# code (requirement of my boss).
Now I can continue with this project.

Greetz,

Geert
Post #1434231
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse