Counting and listing

[ccdok]

I have a table that looks like this:

person dateTime status
--------------------------
...
jke 15-05-2004 1
jke 15-05-2004 2
abc 16-05-2004 1
abc 17-05-2004 2
cde 17-05-2004 1
jke 17-05-2004 1
jke 17-05-2004 2
...

(People log in and log out of a system.)

I need to pull out all a list of those "persons" that have a "status" 1, but not a "status" 2. In other words, they have one more "status" 2 than 1.. For that day.

What would the SQL be for this?

 

[r937]

well, which is it?

they don't have a status 2?

or they have one more status 2 than status 1?

or did you mean one more status 1 than status 2?


in any case (no pun intended) you can probably work it out from this:

select person
     , datefield
  from yourtable
group
    by person
     , datefield
having sum(case when status=2
                then 1 else 0 end)
     = sum(case when status=1
                then 1 else 0 end)
     + 1

rudy

http://r937.com/

SQL Consulting