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 - 2007-09-19 : 04:30:04

Hi,

I'm trying to add an extra column brought back onto a query I already have that is functioning properly, but I'm not sure exactly how to do it.

I am looking to add this line

SELECT COUNT(*) FROM tblInvalidLoginAttempts WHERE nameOnline like '%@%'

To the query below, but I want this above statement to be included on the group by. What I mean by that is I want the above query to return a specific value for each row brought back according to the
"GROUP BY CONVERT(varchar(10),LAL.attemptDate,112)" not a single value for all.

Thanks very much for any help!

mike123



 SELECT CONVERT(varchar(10),LAL.attemptDate,112) as attemptDate, 
COUNT(LAL.nameOnline) AS Total_Invalid_Logins,
COUNT(DISTINCT LAL.nameOnline) AS Total_Invalid_Logins_Unique, 

 FROM tblInvalidLoginAttempts LAL 
WHERE DateDiff(dd, LAL.attemptDate, GetDate()) < 2
GROUP BY CONVERT(varchar(10),LAL.attemptDate,112)
ORDER BY attemptDate DESC

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-19 : 04:35:01

I believe you have proper index on the tblInvalidLoginAttempts table?
If so, none of them are used the way the query above is written.

Try this

SELECT		DATEADD(DAY, DATEDIFF(DAY, 0, AttemptDate), 0) AS AttemptDate,
		COUNT(NameOnline) AS TotalInvalidLogins,
		COUNT(DISTINCT NameOnline) AS TotalInvalidLoginsUnique,
		SUM(CASE WHEN NameOnline LIKE '%@%' THEN 1 ELSE 0 END) AS Yak
FROM		tblInvalidLoginAttempts 
WHERE		AttemptDate >= DATEADD(DAY, DATEDIFF(DAY, 2, CURRENT_TIMESTAMP), 0)
GROUP BY	DATEADD(DAY, DATEDIFF(DAY, 0, AttemptDate), 0)
ORDER BY	DATEADD(DAY, DATEDIFF(DAY, 0, AttemptDate), 0) DESC

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-19 : 04:39:01

This might be faster?

SELECT		DATEADD(DAY, DATEDIFF(DAY, 0, AttemptDate), 0) AS AttemptDate,
		COUNT(NameOnline) AS TotalInvalidLogins,
		COUNT(DISTINCT NameOnline) AS TotalInvalidLoginsUnique,
		SUM(SIGN(CHARINDEX('@', NameOnline))) AS Yak
FROM		tblInvalidLoginAttempts 
WHERE		AttemptDate >= DATEADD(DAY, DATEDIFF(DAY, 2, AttemptDate), 0)
GROUP BY	DATEADD(DAY, DATEDIFF(DAY, 0, AttemptDate), 0)
ORDER BY	DATEADD(DAY, DATEDIFF(DAY, 0, AttemptDate), 0) DESC

Or this

SELECT		DATEADD(DAY, DATEDIFF(DAY, 0, AttemptDate), 0) AS AttemptDate,
		COUNT(NameOnline) AS TotalInvalidLogins,
		COUNT(DISTINCT NameOnline) AS TotalInvalidLoginsUnique,
		SUM(CASE WHEN CHARINDEX('@', NameOnline) > 0 THEN 1 ELSE 0 END) AS Yak
FROM		tblInvalidLoginAttempts 
WHERE		AttemptDate >= DATEADD(DAY, DATEDIFF(DAY, 2, AttemptDate), 0)
GROUP BY	DATEADD(DAY, DATEDIFF(DAY, 0, AttemptDate), 0)
ORDER BY	DATEADD(DAY, DATEDIFF(DAY, 0, AttemptDate), 0) DESC

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

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-09-19 : 06:06:16

Hey Peso,

First one worked perfectly, and instantly.

Thanks so much!
Mike123

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources