CODEWITHSUNDEEP
sqldatabase
user2195836
user2195836

Reputation: 45

SQL Determine flag of a field

I have a database that's used to keep a history of the registers that are open and closed

id | name | assigned_to | state | created_on | created_by
---------------------------------------------------------
1  | Jay  | 1           | OPEN  | 01/01/2011 | 1
2  | Kay  | 2           | OPEN  | 01/01/2011 | 1
3  | Jay  | 1           | CLOSED| 01/01/2111 | 1

"assigned_to" is the employee's id. "name" is the name of the register that they used.

What the database is saying is that "Jay" has been open and closed but "Kay" is not closed at all. I can't figure out a cleaver way to determine how to get only the register that is currently open. Any ideas on how to approach this? Keep in mind that there will be thousands of these as time progresses.

Upvotes: 0

Views: 43

Answers (3)

Joachim Isaksson
Joachim Isaksson

Reputation: 180917

You can simply use ROW_NUMBER() to get the latest value per name, and list those with the latest state equal to OPEN;

WITH cte AS (
  SELECT *, 
    ROW_NUMBER() OVER (PARTITION BY name ORDER BY created_on DESC, id DESC) rn
  FROM myTable
)
SELECT id, name, assigned_to, state, created_on, created_by 
FROM cte WHERE rn=1 AND state='OPEN';

An SQLfiddle to test with.

Upvotes: 0

Joe
Joe

Reputation: 6827

This will produce the rows with the latest created_on (by name) only if it has a state of "CLOSED". 

select 
  *
from
  regs a
  join (select name,max(created_on) created_on
          from regs
         group by name)  b
        on a.name = b.name
where
  a.created_on = b.created_on
  and state = 'CLOSED'

SQL Fiddle here

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269743

Making a few assumptions, you can solve this as a "set-within-sets" subquery. The major assumption is that a name is "closed" only once and is not opened again afterwards. The following query checks that there is no closed state:

select r.name
from registers r
group by r.name
having sum(case when state = 'Closed' then 1 else 0 end) = 0;

If a name can be opened after being closed, then you want to check the most recent time of each. You can try this:

select r.name
from registers r
group by r.name
having sum(case when state = 'Closed' then 1 else 0 end) = 0 or
       max(case when state = 'Closed' then id end) > max(case when state = 'Opened' then id end);

Upvotes: 2

Related Questions