February 5, 2013 at 9:02 am
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
MichelHoekzemaUser Login06-12-12 15:29:3244989
MichelHoekzemaNiet beschikbaar06-12-12 15:29:3544988
MichelHoekzemaNiet beschikbaar06-12-12 15:29:4144987
MichelHoekzemaUser Logout06-12-12 15:29:4644986
MichelHoekzemaUser Login07-12-12 09:54:2944953
MichelHoekzemaNiet beschikbaar07-12-12 09:54:3344952
MichelHoekzemaNiet beschikbaar07-12-12 09:54:4044951
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
February 5, 2013 at 9:35 am
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
February 5, 2013 at 6:06 pm
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[/url]
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 6, 2013 at 3:08 am
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
February 6, 2013 at 3:57 am
Sorry,
I meant Jason-299789 instead of Say Hey Kid. My apologies.
Greetz,
Geert
February 6, 2013 at 5:08 am
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
March 22, 2013 at 6:29 am
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply