Excluding Records in SQL - 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

General SQL Server Forums

New to SQL Server Programming

Excluding Records in SQL

Author

Topic

Jaypoc
Yak Posting Veteran

58 Posts

Posted - 2009-06-29 : 15:53:15

I'm trying to generate a report of people who are still running an old version of a client application. Our application logs all accesses via a Microsoft SQL Server 2000 table which looks like this (with additional columns for type of event reported, and specific messages)

TABLE: dbo.report
username | datestamp | version
---------|----------------|---------
"tom" | 1/1/2009 8:00 | "v2.01.5"
"jason" | 1/1/2009 12:00 | "v2.01.5"
"steve" | 1/2/2009 12:00 | "v2.01.6"
"steve" | 1/3/2009 12:00 | "v2.01.7"
"jason" | 1/3/2009 13:00 | "v2.01.7"
"tom" | 1/4/2009 11:00 | "v2.01.5"
... several million rows in the table

We need to extract a list of people who have used the application with in a certain range of dates but have not run version v2.01.7

I have tried:


SELECT DISTINCT username 
FROM dbo.report
WHERE (datestamp > CONVERT(DATETIME, '2009-06-25 00:00:00', 102)) 
  AND NOT EXISTS(
      SELECT DISTINCT username 
      FROM dbo.report 
      WHERE (version = 'v2.01.7') AND 
        (datestamp > CONVERT(DATETIME, '2009-06-25 00:00:00', 102))
  )
ORDER BY username

This runs but doesn't return any results. If i run the 2 queries isolated, the main one returns 1285 rows. The inner one returns 881. The excluded list should contain 404 user names

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-29 : 16:00:06

Your subselect (in the not exists) has no relation to the outer query.
SELECT DISTINCT username
FROM dbo.report r1
WHERE (datestamp > CONVERT(DATETIME, '2009-06-25 00:00:00', 102))
AND NOT EXISTS(
SELECT DISTINCT username
FROM dbo.report r2
WHERE r1.username = r2.username and
(version = 'v2.01.7') AND
(datestamp > CONVERT(DATETIME, '2009-06-25 00:00:00', 102))
)
ORDER BY username

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-29 : 16:00:40

Try

SELECT		UserName
FROM		dbo.Report
WHERE		DateStamp >= '20090625' 
GROUP BY	UserName
HAVING		MAX(CASE WHEN [Version] = 'v2.01.7' THEN 1 ELSE 0 END) = 0

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources