Help with NOT EXISTS - 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 2000 Forums

SQL Server Development (2000)

Help with NOT EXISTS

Author

Topic

crudmop
Starting Member

14 Posts

Posted - 2004-02-12 : 13:14:09

I am currently trying to grab some data from an hourly log. What I need to do is grab all of the days in the month when a log HAS NOT been created by a user, and display the userIDs.

I can do a simple
"SELECT userid,name FROM users WHERE NOT exists (SELECT jdate from hours WHERE jdate='2/9/2004' AND users.userID=hours.userid)", which will give me the correct result.

How do I do this for an entire month? I can easily create a list of dates in .asp and feed the WHERE clause, which I tried doing (create a large WHERE jdate IN xxx...) but it does not display the correct result, meaning, all of the users are shown. (Because this is an OR). I found that even if I add a single date (ie WHERE jdate='2/9/2004' AND jdate='2/10/2004') the query will run but does not give the correct result.

Does anyone know how to do this more efficiently, better, etc? Thanks in advance for any help!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-12 : 13:34:05

For an entire month, you could use DATEPART or BETWEEN:

WHERE DATEPART(m, jdate) = 2 AND DATEPART(yy, jdate) = 2004 AND users...

WHERE jdate BETWEEN '02-01-2004' AND '02-29-2004' AND users...

Tara

crudmop
Starting Member

14 Posts

Posted - 2004-02-12 : 14:52:46

Ahh - I should have been a little clearer. I of course expect everyone to immediately know what's going on in my head. :)

I actually tried the full month, and it returns all users regardless if there are log files that are missing or not.

The other part, which I failed to add in the original post - I do not want it to look for weekend dates, as there will never be (or will rarely be) logfiles created on the weekend, so I will just ignore them. I have a function which pulls out the weekend dates and passes on only the dates necessary to the query.

It's odd to me that the query will accept a single date and work fine, but does not work with any additional "ANDs". I am clearly doing something wrong, but, what?

theGrimm
Starting Member

4 Posts

Posted - 2004-02-13 : 01:11:25

To exclude, say, weekends, you could use the datepart function:

e.g
WHERE datepart("dw", jdate) NOT IN (5, 6)

What datepart does is return a number from 1 to seven corresponding to a day of the week. you can use "SET DATEFIRST <??>" to set which day of the week is the first day of the week. For example,

SET DATEFIRST 1
....
WHERE datepart("dw", jdate) NOT IN (6, 7)
....

Will set Monday as the first day of the week, and then the where clause will exclude Saturdays and Sundays.

theGrimm

crudmop
Starting Member

14 Posts

Posted - 2004-02-13 : 10:23:18

Tara, theGrimm, thanks so much for your help so far. I am certainly inching closer.

Exclusion of the weekends on that side is great. I guess the issue is still that I need to read every day of the month into the query. By selecting "WHERE it is in month X" and "not on the weekends", I will receive a result of TRUE if it hits ANY DAY of the month with a log.

However, I need it to check each date in the month 1 at a time, and insure that each day has a log recorded for that date. Now, I can feed them in a full list, but by doing a WHERE IN would make the statement an "or" and I need an "and" I think. What's strange is that the query above works "SELECT userid,name FROM users WHERE NOT exists (SELECT jdate from hours WHERE jdate='2/9/2004' AND users.userID=hours.userid)" as long as I keep only 1 date in the where clause. If I add a second, then all users are shown regardless of if they missed days or not.

Is there something I am missing in adding multiple "ands" to that query? I am sure the syntax needs reworking to get it to function, I am just at a wall I guess.

MuadDBA

628 Posts

Posted - 2004-02-13 : 11:05:31

Can you give us some sample data for the tables so we might be able to see what is going on?

crudmop
Starting Member

14 Posts

Posted - 2004-02-14 : 00:13:09

Sure, sorry.

The 2 tables I am dealing with are a users table, containing users for the system , and a logs table with some log data.

The important columns for this query being:

users:
userid name
1 test user 1
2 test user 2
3 test user 3

hours
hoursID userid jdate
1 1 02/09/2003
2 1 02/10/2003
3 2 02/10/2003

I will not enter it here, but, lets assume that userid 1 has all 30 days with an associated log file

Now, what I need it to do is to run down the column of users, check to make sure that all dates for a given month (excluding weekends) have a log record associated with them.

If I run the query "SELECT userid,name FROM users WHERE NOT exists (SELECT jdate from hours WHERE jdate='2/9/2004' AND users.userID=hours.userid)"

I return the 2 usernames that do not have log files for that day, userid 2 & 3. Which is exactly what I need.

If I add to the previous query "SELECT userid,name FROM users WHERE NOT exists (SELECT jdate from hours WHERE jdate='2/9/2004' AND jdate='2/10/2003 AND users.userID=hours.userid)"

I return all users in the users table, regardless. So, the query functions only as long as a single date is entered, anything else, and the request I need fails.

Sorry, I should have posted this up in the beginning.

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2004-02-17 : 22:13:07

Think this'll work: Create a temp table with all days in the month that need logs by running a loop and inserting all dates in the month that aren't weekends. Then, cross join this temp table with the Users table to produce a cartesian product (no WHERE clause) to form a derived table, and outer join this on the hours table. The null rows on the hours table show what's missing.
Sample code:


DECLARE @Date DATETIME, @To DATETIMe 
SET @Date = YourStartDate
SET @To = DATEADD(mm,1,@Date)
CREATE TABLE #DaysInMonth (MonthDay DATETIME)
WHILE @Date < @To --Increment date in loop to fill temp table with all dates that are not weekends
 IF DATEPART(dw,@Date) NOT IN(6,7) INSERT INTO #DaysInMonth VALUES(@Date)
 SET @Date = DATEADD(d,1,@Date)

SELECT dt.UserID,dt.MonthDay FROM (SELECT UserID,MonthDay FROM Users,#DaysInMonth) dt LEFT JOIN Hours h ON h.UserID = dt.UserID AND h.jDate = dt.MonthDay WHERE h.UserID IS NULL

Sarah Berger MCSD

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-02-17 : 23:52:24

You will need to add BEGIN...END tags to the WHILE loop in Sarah's code:


DECLARE @Date DATETIME, @To DATETIMe 
SET @Date = YourStartDate
SET @To = DATEADD(mm,1,@Date)
CREATE TABLE #DaysInMonth (MonthDay DATETIME)
WHILE @Date < @To --Increment date in loop to fill temp table with all dates that are not weekends
BEGIN
 IF DATEPART(dw,@Date) NOT IN(6,7) INSERT INTO #DaysInMonth VALUES(@Date)
 SET @Date = DATEADD(d,1,@Date)
END

SELECT dt.UserID,dt.MonthDay FROM (SELECT UserID,MonthDay FROM Users,#DaysInMonth) dt LEFT 
 JOIN Hours h ON h.UserID = dt.UserID AND h.jDate = dt.MonthDay WHERE h.UserID IS NULL

Roberta
Starting Member

2 Posts

Posted - 2004-02-23 : 15:20:01

This won't work because jdate can't be 2/9/2004 and 2/10/2004. (SELECT jdate from hours WHERE jdate='2/9/2004' AND jdate='2/10/2003 AND users.userID=hours.userid)

Try this instead. SELECT jdate from hours WHERE (jdate='2/9/2004' OR jdate='2/10/2003) AND users.userID=hours.userid.

When putting the parenthesis around the OR it is saying the OR is just for that section. So it will give all records that have dates of either 2/9/2004 or 2/10/2003 that have the users.userID = the hours.userid.

crudmop
Starting Member

14 Posts

Posted - 2004-02-27 : 09:45:03

Again all, thanks so much for your help!

As per simondeutsch,mohdowais' replies, I gave that a shot and it's kicking out an "internal query processor error" - it functions fine up to the insert, but craps out when I add the select statement at the bottom. (I am using this as a SP). Sorry if this is a noob issue.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources