Using Not Exists in a Query 1

[skurkal]

I have a table with user states in it. I worte the following query to find persons who have never been in a 'ready' state for a given period of time. This query takes forever to run. Any way to make it better?


SELECT distinct A.UserName, A.UserID
FROM dbo.Activity A
WHERE A.BeginTime >= '2008-05-05 04:00:00'
and A.BeginTime < '2008-05-12 04:00:00'
and not exists
(SELECT *
FROM dbo.Activity A2
WHERE A2.BeginTime >= '2008-05-05 04:00:00'
and A2.BeginTime < '2008-05-12 04:00:00'
and A2.State = 'Ready'
and A2.UserID = A.UserID)

Also I have to find the people who have newly appeared in this catogory. So given a dataset of people who were never ready the previous week (obtained from the query above), give me those who newly appeared in this group yesterday.

So here is what is needed :

If previous week people who were not in 'ready' state were identified as

A
B
C
D

And yesterday we now have

A
B
C
D
Z

I need to identify Z

I tried to write a query with another not exists clause but it takes for ever to run.

Thanks,
Sumi.

 

[RiverGuy]

Try a Left Outer Join

 

[bborissov]

SELECT Activity.UserName, Activity.UserID
       FROM Activity
LEFT JOIN (SELECT UserID
                  FROM Activity
           WHERE BeginTime >= '2008-05-05 04:00:00'  
             and BeginTime <  '2008-05-12 04:00:00'
             and State = 'Ready') Tbl1
ON  Activity.UserId = Tbl1.UserId 
WHERE BeginTime >= '2008-05-05 04:00:00'  
 AND  BeginTime <  '2008-05-12 04:00:00'
 AND  Tbl1.UserId IS NULL

not tested.


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[bborissov]

SELECT Activity.UserName, Activity.UserID
       FROM Activity
WHERE BeginTime >= '2008-05-05 04:00:00'  
 AND  BeginTime <  '2008-05-12 04:00:00'
GROUP BY Activity.UserName, Activity.UserID
HAVING SUM(CASE WHEN State = 'Ready'
                THEN 1
                ELSE 0 END) = 0

again not tested.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[skurkal]

Thanks! both options work very well

 

[ESquared]

Borislav,

Your last query is what I was going to suggest, too. Though, for philosophical reasons I prefer Max() instead of Sum() here.

Skurkal, that last query will surely be more efficient than the join.

I do see one wrinkle, though. You said "find persons who have never been in a 'ready' state for a given period of time." Depending on how you're identifying the people, the assumption you're making here is that the person will have at least one other status during that time frame. However, the list of people you want to examine could be bigger than that. Can you guarantee that for each person you're checking to have no "Ready" rows in the defined period, he also has at least one other row without? For example, what if Z became ready yesterday but had no status at all the previous week?

Your second request actually solves the problem, because now you've defined the set of people you want to examine by those who became ready yesterday. So we don't need to rely on an outside source to get the extra rows (of people who had no status at all last week).

SELECT Activity.UserName, Activity.UserID
       FROM Activity
WHERE
   (
      BeginTime >= '2008-05-05 04:00:00'  
      AND BeginTime < '2008-05-12 04:00:00'
   ) OR (
      BeginTime >= '2008-05-15'
      AND BeginTime < '2008-05-16'
   )
GROUP BY Activity.UserName, Activity.UserID
HAVING
   Coalesce(Max(
      CASE WHEN
         State = 'Ready'
         AND BeginTime >= '2008-05-05 04:00:00'  
         AND BeginTime < '2008-05-12 04:00:00'
      THEN 1 ELSE 0 END
   ), 0) = 0 -- not ready last week
   AND Max(
      CASE WHEN
         State = 'Ready'
      BeginTime >= '2008-05-15'
      AND BeginTime < '2008-05-16'
      THEN 1 ELSE 0 END
   ) = 1 -- but ready yesterday