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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help combining three queries into one

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 ASC

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

Using 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 information
SELECT DATEDIFF(n,?,?) AS Duration

Is 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 | duration

Any 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]
Go to Top of Page

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,?,?)
Go to Top of Page

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]
Go to Top of Page

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)
Go to Top of Page

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]
Go to Top of Page

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.
Go to Top of Page

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]
Go to Top of Page

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 this
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 = '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.
Go to Top of Page

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.
Go to Top of Page

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 tables
aData | aTIme | UserIP | WebAddress

So 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.
Go to Top of Page

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]
Go to Top of Page

wales321
Starting Member

27 Posts

Posted - 2013-04-27 : 08:38:30
Yes that works! You've been a great help, many thanks.
Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-04-27 : 09:13:06
You are welcome. Glad to help.
Go to Top of Page
   

- Advertisement -