help with query - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

help with query

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-02-25 : 07:57:35

Hi,

Needing a lil help with this query if anyones able to help out.

I have a webapp where all user logins are stored into a "login_log" table. The query below select the latest logins, however I want to modify it so that when a user does multiple logins within 30 minutes their latest logins do not count.

Not exactly sure how to build this query, but basically exclude the below results from how it is now

something like

SELECT userID from tblLogins_Log where loginDate

anyways here is what I have below

any help is very much appreciated !! as always

mike123

table structure



CREATE TABLE [dbo].[tblLogins_Log](
	[loginID] [int] IDENTITY(1,1) NOT NULL,
	[userID] [int] NULL,
	[IP] [varchar](15) NOT NULL,
	[loginDate] [datetime] NOT NULL
) ON [PRIMARY]

query:



SELECT TOP 5 userID, nameOnline, city, age,SP.stateProv,


	case when exists (select userID from tblActive_Users UA WHERE UD.userid = UA.userID ) then 
		'Y' 
	else 
		'N' 
	end as online 

  FROM tblUserDetails UD  


JOIN tblStateProv SP on SP.stateProvID = UD.stateProvID

WHERE (active = 1 or active =4)  and   ORDER BY lastLoggedIn DESC

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-25 : 08:15:02

You will get much more accurate and faster answer if you provide some sample data and expected output.
Have you forgotten everything we have teached you?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-25 : 08:18:30

[code]SELECT TOP 5
{ud | sp | ua}.userID,
{ud | sp | ua}.nameOnline,
{ud | sp | ua}.city,
{ud | sp | ua}.age,
sp.stateProv,
CASE
WHEN ua.userID IS NULL THEN 'N'
ELSE 'Y'
END AS online
FROM tblUserDetails AS ud
INNER JOIN tblStateProv AS sp ON sp.stateProvID = ud.stateProvID
LEFT JOIN tblActive_Users AS ua ON ua.userID = ud.userid
WHERE {ud | sp | ua}.active IN (1, 4)
AND
ORDER BY {ud | sp | ua}.lastLoggedIn DESC[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-25 : 08:37:57

[code]-- Prepare sample data. This is only for mimic your environment and is not part of the solution.
DECLARE @Sample TABLE (loginID INT, userID INT, loginDate DATETIME)

INSERT @Sample
SELECT 1, 1, '20080225 14:45:15' UNION ALL
SELECT 3, 1, '20080225 15:27:27' UNION ALL
SELECT 7, 1, '20080225 15:30:05' UNION ALL
SELECT 4, 1, '20080225 15:45:16' UNION ALL
SELECT 5, 2, '20080225 16:01:43' UNION ALL
SELECT 9, 2, '20080225 15:30:43'

-- You can turn this solution into a view
;WITH Yak (userID, lastLoggedIn, loginCount)
AS (
SELECT userID,
MAX(loginDate) AS lastDate,
1
FROM @Sample
GROUP BY userID

UNION ALL

SELECT s.userID,
s.loginDate,
y.loginCount + 1
FROM Yak AS y
INNER JOIN @Sample AS s ON s.userID = y.userID
WHERE s.loginDate < y.lastLoggedIn
AND DATEDIFF(SECOND, s.loginDate, y.lastLoggedIn) <= 1800
)

SELECT userID,
MIN(lastLoggedIn) AS lastLoggedIn,
MAX(loginCount) AS loginCount
FROM Yak
GROUP BY userID[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-25 : 09:01:07

Output from above sample data is

userID	lastLoggedIn		loginCount
------	-------------------	----------
1	2008-02-25 15:27:27	3
2	2008-02-25 16:01:43	1

E 12°55'05.25"
N 56°04'39.16"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-02-25 : 09:33:28

Hey Peso,

Apologizes for not posting more info, I didnt realize we were looking at something this advanced. I'm reviewing all your suggstions right now.

1 small thing I noticed was "WHERE (active = 1 or active =4) " you changed to "active IN (1, 4)" I'm assuming this is probably your preferred method because its not only neater, but faster too? I have alot of queries perhaps I could optimize by changing them to this way.

Will be back shortly with the rest,doing some testing :)

thanks once again,
mike123

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-25 : 09:39:48

There is no difference in query.
This is my preferred way to write because it minimizes the error possibilities when having multiple criterias.
I try to write to use as many ANDs as possible, instead of mixing ANDs and ORs.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-25 : 09:42:12

You can use the code above to write your query as you need.
SELECT TOP 5 * FROM {View} AS v
INNER JOIN (some other tables)
ORDER BY v.lastLoggedIn DESC

E 12°55'05.25"
N 56°04'39.16"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-02-25 : 11:07:31

Hi Peso,

Good to know, and I think a good policy for me to follow as well.

As far as the query goes, I have not used a view before so I'm trying to figure out exactly whats going on. I'm seeing maybe a couple things here, so I'll be back later with some sample data/ expected output.

Thanks again!
Mike123

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-25 : 11:20:42

Create the view like this

CREATE VIEW dbo.vwLastLoggedIn
AS

;WITH Yak (userID, lastLoggedIn, loginCount)
AS (
	SELECT		userID,
			MAX(loginDate) AS lastDate,
			1
	FROM		tblLogins_Log
	GROUP BY	userID

	UNION ALL

	SELECT		s.userID,
			s.loginDate,
			y.loginCount + 1
	FROM		Yak AS y
	INNER JOIN	tblLogins_Log AS s ON s.userID = y.userID
	WHERE		s.loginDate < y.lastLoggedIn
			AND DATEDIFF(SECOND, s.loginDate, y.lastLoggedIn) <= 1800
)

SELECT		userID,
		MIN(lastLoggedIn) AS lastLoggedIn,
		MAX(loginCount) AS loginCount
FROM		Yak
GROUP BY	userID

Then you can use this view as a virtual table to join in any query.

SELECT * FROM vwLastLoggedIn

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-25 : 11:25:48

quote:
Originally posted by mike123

I didnt realize we were looking at something this advanced.

There is nothing advanced going on here.

I fetch all userid and their corresponding last logindate. Then I check to see for any userid if there are any more records that are at most 30 minutes (1800 seconds) older. If there are, I replace lastloggedin with these.
Then I do this iteration again; see if there are any records at most1800 seconds older. Finally there are no more records within 30 minutes and the CTE is finished.

I then display the userid, minimum of logindate and number of times I iterated for that userid.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-28 : 17:40:43

quote:
Originally posted by mike123

I'm seeing maybe a couple things here, so I'll be back later with some sample data/ expected output.

Later is now?

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources