filtering out result set with HAVING

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

Transact-SQL (2000)

filtering out result set with HAVING

Author

Topic

SQL_Rookie
Starting Member

32 Posts

Posted - 2007-02-14 : 12:17:19

I have a query to find all consumers with or whitout orders but if they have orders the last order must be created over 21 days ago so I go this....but wondering if there a BETTER WAY...
DECLARE @DATE DATETIME
SET @DATE = DATEADD(dd, -21, GETUTCDATE())
select c.consumer_id
from Consumers c
where
exists (select * from Orders O
where c.CONSUMER_ID = O.CONSUMER_ID
group by c.CONSUMER_ID
having max(h.created_dt) < @DATE)
or not exists (select * from Orders O
where c.CONSUMER_ID = O.CONSUMER_ID)

I was wondering if I can do this with an outer of the consumers and grab all the Orders that meet the criteria but can not figure it out.

Kristen
Test

22859 Posts

Posted - 2007-02-14 : 13:45:34

Would this give the same result?


SELECT *
FROM Consumers AS C
WHERE NOT EXISTS
(
    SELECT * 
    FROM Orders AS O
    WHERE C.CONSUMER_ID = O.CONSUMER_ID
          AND O.created_dt >= @DATE
)

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-14 : 15:05:49

SELECT c.*
FROM Consumers AS c
LEFT JOIN Orders AS o ON o.Consumer_ID = c.Consumer_ID AND o.Created_DT < DATEADD(day, -20, GETUTCDATE())

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2007-02-15 : 06:22:11

That's gonna give you multiple hits on CONSUMERS if they have multiple ORDERS that satisfy the date criteria ...

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-15 : 07:11:55

True. But a simple DISTINCT c.Consumer_ID will take care of that.

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2007-02-15 : 08:11:32

Well I'm guessing that will be more CPU than a NOT EXISTS ... but it would need testing of course!

Kristen