Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
wales321
Starting Member
27 Posts |
Posted - 2013-04-24 : 15:09:24
|
Hi,I have 3 queries which i have currently split up in my Java method to achieve the result i need. The problem i have now though is if i enter an incorrect parameter instead of getting an empty table of results i end up with errors and NullPointerExceptions, i assume because of my use of ArrayLists to store dates. Hopefully you can help me get this into one query and i can get rid of these errors. I have three queries where i have the same problem but im hoping if i get one working i should be able to transfer the logic across to the other 2.The query is to find the log in duration's for a particular user. The only input will be a username and i want to return all log in durations of that user.Currently i am finding all user log ins like this:SELECT aDate, aTime, userIP FROM HotSpotAccount WHERE aUser = ? AND Message Like '%logged in%' ORDER BY aDate ASC, aTime ASCI am then storing the results from this into an ArrayList.To get the log out times i am using this, and also storing them in a separate ArrayList.SELECT aDate, aTime FROM HotSpotInfoDebug WHERE Username = ? AND Ip = ? AND Message Like '%logged out%' ORDER BY aDate ASC, aTime ASCUsing the two ArrayLists i then create a format which i can pass to the statement below, this involves combining the date and time into one string and using the log in/out pairs in my ArrayLists to get this informationSELECT DATEDIFF(n,?,?) AS DurationIs there a way i can put it all into one statement returning it as one ResultSet with the following output Log in Date/time | Log Out Date/time | durationAny help would be great, Thanks! |
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-04-24 : 15:49:44
|
Try the following:[CODE]WITH T1 AS (SELECT aDate, aTime, userIP FROM HotSpotAccount WHERE aUser = ? AND Message Like '%logged in%' ORDER BY aDate ASC, aTime ASC),T2 AS (SELECT aDate, aTime FROM HotSpotInfoDebug WHERE Username = ? AND Ip = ? AND Message Like '%logged out%' ORDER BY aDate ASC, aTime ASC)SELECT T1.aDate, T1.aTime, T2.aDate, T2.aTime, DATEDIFF(n,?,?) AS Duration where T1.aUser = T2.Username;[/CODE] |
|
|
wales321
Starting Member
27 Posts |
Posted - 2013-04-24 : 15:57:24
|
Would that still not require separate queries? I would need the IP address from the first query and i would also need the all of the log in date/times and log out date/times for the DATEDIFF(n,?,?) |
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-04-24 : 16:24:16
|
You may have to tweak this code (around Duration) some more to get exactly what you need ...[CODE]WITH T1 AS (SELECT aDate, aTime, userIP FROM HotSpotAccount WHERE aUser = ? AND Message Like '%logged in%')SELECT T1.aDate, T1.aTime, aDate, aTime, DATEDIFF(n, aDate+aTime, T1.aDate+T1.aTime) AS Duration FROM HotSpotInfoDebug T2 WHERE Ip = T1.userIP AND Message Like '%logged out%' and T1.aUser = T2.Username ORDER BY aDate ASC, aTime ASC;[/CODE] |
|
|
wales321
Starting Member
27 Posts |
Posted - 2013-04-24 : 16:47:56
|
Thanks, i am trying to test it and got this SQL exception. I am looking for a syntax error but cant find one yet. Syntax error in SQL statement "WITH T1 AS[*] (SELECT ADATE, ATIME, USERIP FROM HOTSPOTACCOUNT WHERE AUSER = 'admin' AND MESSAGE LIKE '%logged in%') SELECT T1.ADATE, T1.ATIME, ADATE, ATIME, DATEDIFF(N, ADATE+ATIME, T1.ADATE+T1.ATIME) AS DURATION FROM HOTSPOTINFODEBUG T2 WHERE IP = T1.USERIP AND MESSAGE LIKE '%logged out%' AND T1.AUSER = T2.USERNAME ORDER BY ADATE ASC, ATIME ASC "; expected "., ("; SQL statement:WITH T1 AS (SELECT aDate, aTime, userIP FROM HotSpotAccount WHERE aUser = 'admin' AND Message Like '%logged in%') SELECT T1.aDate, T1.aTime, aDate, aTime, DATEDIFF(n, aDate+aTime, T1.aDate+T1.aTime) AS Duration FROM HotSpotInfoDebug T2 WHERE Ip = T1.userIP AND Message Like '%logged out%' and T1.aUser = T2.Username ORDER BY aDate ASC, aTime ASC [42001-170] 42001/42001 (Help) |
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-04-24 : 17:08:04
|
Which version of SQL Server are you using?try this:[CODE] SELECT T1.aDate, T1.aTime, aDate, aTime, DATEDIFF(n, aDate+aTime, T1.aDate+T1.aTime) AS Duration FROM (SELECT aDate, aTime, userIP FROM HotSpotAccount WHERE aUser = ? AND Message Like '%logged in%')AS T1, HotSpotInfoDebug T2 WHERE Ip = T1.userIP AND Message Like '%logged out%' and T1.aUser = T2.Username ORDER BY aDate ASC, aTime ASC;[/CODE] |
|
|
wales321
Starting Member
27 Posts |
Posted - 2013-04-24 : 17:16:54
|
I am using a h2 database. Its now telling me i have ambiguous column names but i am working my way through to see if i can fix that. |
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-04-24 : 17:28:58
|
This is Microsoft SQL Server site.I am not familiar with H2 database, but you may want to try something like this:[CODE]SELECT T1.aDate, T1.aTime, T2.aDate, T2.aTime, DATEDIFF(n, T2.aDate+T2.aTime, T1.aDate+T1.aTime) AS Duration FROM (SELECT aDate, aTime, userIP FROM HotSpotAccount WHERE aUser = ? AND Message Like '%logged in%')AS T1, HotSpotInfoDebug T2 WHERE T2.Ip = T1.userIP AND T2.Message Like '%logged out%' and T1.aUser = T2.Username ORDER BY T1.aDate ASC, T1.aTime ASC;[/CODE] |
|
|
wales321
Starting Member
27 Posts |
Posted - 2013-04-24 : 19:44:04
|
Sorry, i had used this site for other SQL questions in the past.I can get it working by adjusting the code slightly like thisSELECT T1.aDate, T1.aTime, T2.aDate, T2.aTime, DATEDIFF(n, T1.aDate+T1.aTime, T2.aDate+T2.aTime) AS Duration FROM (SELECT aDate, aTime, userIP FROM HotSpotAccount WHERE aUser = '3036364' AND Message Like '%logged in%' ORDER BY aDate ASC, aTime ASC)AS T1, HotSpotInfoDebug T2 WHERE T2.Ip = T1.userIP AND T2.Message Like '%logged out%' ORDER BY T1.aDate ASC, T1.aTime ASC;I had to swap T1 and T2 around in DATEDIFF and i also added ordering to the second SELECT as i cannot guarantee the order of the records in my table. The only problem is i had to remove AND T1.aUser = T2.Username as it did not recognise the column name T1.aUser. I can't see why it doesn't accept that though as it is a valid column name and T1 is also used in the same WHERE statement. |
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-04-24 : 20:05:25
|
We are here to help each other and learn from each other, keep posting as long as your questions are answered...Glad to hear that you are able to make your query work. |
|
|
wales321
Starting Member
27 Posts |
Posted - 2013-04-26 : 15:18:13
|
Sorry to bring this topic up again, but im struggling to modify this query to work with the other two queries i mentioned in my first post. Below is how the query looks now with a few modifications to avoid negative times.SELECT T1.adate, T1.atime, T2.adate, T2.atime, Datediff(n, T1.adate + T1.atime, T2.adate + T2.atime) AS Duration FROM (SELECT adate, atime, userip FROM hotspotaccount WHERE auser = ? AND message LIKE '%logged in%' ORDER BY adate ASC, atime ASC)AS T1, hotspotinfodebug T2 WHERE T2.ip = T1.userip AND T2.message LIKE '%logged out%' AND T2.username = ? AND T1.adate + T1.atime < T2.adate + T2.atime ORDER BY T1.adate ASC, T1.atime ASC I want to modify this so that instead of finding the duration between the two time slots i can return the web requests between those times.I will be getting the requests from a table called WebProxy which has the following tablesaData | aTIme | UserIP | WebAddressSo i want to do a search returning aDate, aTime and WebAddress where the userIP matches the IP in the searches above and the date and time are between T1 TIme/Date and T2 TIme/Date. I know all the bits separately its just putting them all together. |
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-04-26 : 21:13:31
|
This may not be the most optimal way to get what you need, but should work.NOTE OF CAUTION: I was not able to test this code as I do not have the DDLs and necessary data. You may encounter syntactical errors as well...[CODE]SELECT TT.userip, WP.adate, WP.atime, WP.WebAddress from (SELECT T1.adate as StartDate, T1.atime as StartTime, T2.adate as EndDate, T2.atime as EndTime, Datediff(n, T1.adate + T1.atime, T2.adate + T2.atime) AS Duration, T1.userip FROM (SELECT adate, atime, userip FROM hotspotaccount WHERE auser = ? AND message LIKE '%logged in%' ORDER BY adate ASC, atime ASC)AS T1, hotspotinfodebug T2 WHERE T2.ip = T1.userip AND T2.message LIKE '%logged out%' AND T2.username = ? AND T1.adate + T1.atime < T2.adate + T2.atime ORDER BY T1.adate ASC, T1.atime ASC ) AS TT INNER JOIN WebProxy WP ON TT.userip = WP.userip AND TT.StartDate <= WP.adate AND TT.StartTime <= WP.atime AND TT.EndDate >= WP.adate AND TT.EndTime >= WP.atime[/CODE] |
|
|
wales321
Starting Member
27 Posts |
Posted - 2013-04-27 : 08:38:30
|
Yes that works! You've been a great help, many thanks. |
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-04-27 : 09:13:06
|
You are welcome. Glad to help. |
|
|
|
|
|
|
|