SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help combining three queries into one
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

wales321
Starting Member

27 Posts

Posted - 04/24/2013 :  15:09:24  Show Profile  Reply with Quote
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!

Edited by - wales321 on 04/24/2013 15:14:48

MuMu88
Aged Yak Warrior

547 Posts

Posted - 04/24/2013 :  15:49:44  Show Profile  Reply with Quote
Try the following:



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;

Go to Top of Page

wales321
Starting Member

27 Posts

Posted - 04/24/2013 :  15:57:24  Show Profile  Reply with Quote
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

547 Posts

Posted - 04/24/2013 :  16:24:16  Show Profile  Reply with Quote
You may have to tweak this code (around Duration) some more to get exactly what you need ...


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;




Edited by - MuMu88 on 04/24/2013 16:40:30
Go to Top of Page

wales321
Starting Member

27 Posts

Posted - 04/24/2013 :  16:47:56  Show Profile  Reply with Quote
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

547 Posts

Posted - 04/24/2013 :  17:08:04  Show Profile  Reply with Quote
Which version of SQL Server are you using?

try this:


   
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;



Go to Top of Page

wales321
Starting Member

27 Posts

Posted - 04/24/2013 :  17:16:54  Show Profile  Reply with Quote
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

547 Posts

Posted - 04/24/2013 :  17:28:58  Show Profile  Reply with Quote
This is Microsoft SQL Server site.

I am not familiar with H2 database, but you may want to try something like this:


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;




Edited by - MuMu88 on 04/24/2013 19:20:12
Go to Top of Page

wales321
Starting Member

27 Posts

Posted - 04/24/2013 :  19:44:04  Show Profile  Reply with Quote
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

547 Posts

Posted - 04/24/2013 :  20:05:25  Show Profile  Reply with Quote
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 - 04/26/2013 :  15:18:13  Show Profile  Reply with Quote
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

547 Posts

Posted - 04/26/2013 :  21:13:31  Show Profile  Reply with Quote
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...



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

Go to Top of Page

wales321
Starting Member

27 Posts

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

MuMu88
Aged Yak Warrior

547 Posts

Posted - 04/27/2013 :  09:13:06  Show Profile  Reply with Quote
You are welcome. Glad to help.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000