+2  A: 

Typically the better way to write those is with EXISTS. The first one would be:

select * from performance p1
where not exists 
    ( select * from performance p2 
      where p2.identifier = p1.identifier and p2.activity = 4 )

This way lets you do a keyed lookup on performance.identifier, rather than potentially having to build a massive list of identifiers in (select identifier from performance where activity=4).

Andy Lester
How can I display the "participant" if is in status 2 and nothing if in other status?.. I think this has to do with the left outer thing.. I'm I wrong?...
OscarRyz
Very helpful btw
OscarRyz
A: 

Firstly, you may have a design issue if you can have a customer with multiple tickets open at the same time. You should ideally have a ticket_id, and then you can perform Andy's query by using ticket_id instead of identifier.

Andrew from NZSG
Well the identifier is the "ticket_id" and this is not a query using the ticket_id but a report to display productivity by week, day, hour, etc. etc. The design comes from a 3rd party product. Do you have any Idea how can I achieve this?
OscarRyz
+2  A: 

I think this should do it.

The first part gets all records that are new, not closed and not in progress. The second part gets all in progress records. We then join them together, we can also sort by identifier by wrapping a 'SELECT * FROM' around this query.

select 
  a.identifier,
  a.participant,
  a.closedate as start
from 
  performance a
where
  a.activity = 1
  and not exists ( select identifier 
                   from performance b 
                   where b.activity = 4 
                   and b.identifier = a.identifier) 
  and not exists ( select identifier 
                   from performance c 
                   where c.activity = 2 
                   and c.identifier = a.identifier) 
UNION ALL
select 
  a.identifier,
  a.participant,
  a.closedate as start
from 
  performance a
where
  a.activity = 2
  and not exists ( select identifier 
                   from performance b 
                   where b.activity = 4 
                   and b.identifier = a.identifier);
Abarax
Uuuhhgg.. .Almost. It turns out all those in 2 are already in 1. Because it is the history, not the status. But... with your advice I'm pretty close. I'll post the resulting monster... :)
OscarRyz
Great news! I'm glad i finally helped someone on here :D
Abarax
;) Abarax, Your post definitely change my perception of what I wanted, thanks a lot. Unfortunately it duplicates the records ( N001 1, and N002 2 ) when I need only one. I tries the solution posted by Bill Karwin below and worked great. Thank anyway +1 to you.
OscarRyz
A: 

What tickets are not closed:

select identifier as closed_identifier 
  from performance where identifier not exists
  (select identifier from performance where activity=4)

Tickets that are being attended:

select identifier as inprogress_identifier, participant performance 
  from performance where activity=2

Unclosed tickets, with the participant of that are being attended:

select * from 
  (select identifier as notclosed_identifier 
    from performance where identifier not exists
    (select identifier from performance where activity=4)) closed 
left join 
  (select identifier as inprogress_identifier, participant performance 
    from performance where activity=2) attended 
on notclosed_identifier=inprogress_identifier
Josh
+2  A: 

Try something like this (I haven't tested it):

SELECT p_new.identifier, COALESCE(p_inprog.activity, p_new.activity) AS activity,
  p_inprog.participant, COALESCE(p_inprog.closedate, p_new.closedate) AS closedate
FROM performance p_new
  LEFT OUTER JOIN performance p_inprog 
    ON (p_new.identifier = p_inprog.identifier AND p_inprog.activity = 2)
  LEFT OUTER JOIN performance p_closed 
    ON (p_new.identifier = p_closed.identifier AND p_closed.activity = 4)
WHERE p_new.activity = 1
  AND p_closed.identifier IS NULL;

I think people believe outer joins are harder than they really are. For example:

A LEFT OUTER JOIN B ON (...condition...)

This returns all rows from A, whether or not there are any matching rows in B. If no rows in B match, treat all columns B.* as NULL in the result set for that row of A. The join condition can be an expression that the row in B must satisfy, or else it isn't included in the join. So, more rows in A will be solo.

Bill Karwin
What is COALESCE?
OscarRyz
COALESCE is a SQL function that returns its first argument that is not NULL. In the example above, rows in p_inprog are NULL if they don't match the outer join condition, so the field in the result "defaults" to the value from p_new.
Bill Karwin
Wow!!!. I don't believe oj are harder, they ARE harder for me I'm staring at the SQL in front of me and I just don't get it ( well it comes clearer each time ) But I would't come to this solution my self.A final question, why is there a need for p_closed.identifier is null?Thanks for the help!
OscarRyz
When p_closed.identifier is null, it means there is no matching row in the last join. Which means no row exists with that identifier value and activity=4. Hence, the ticket has not been closed.
Bill Karwin
+1  A: 

I would suggest that what you want is the earliest record (presumably, but not necessarily the one with activity=1) and the most recent record (regardless of activity number). If the activity of the most recent record is 4 then the ticket is closed. otherwise, the participant is the current holder of the ticket. There is a potential bug introduced by just matching on activity = 4 if the ticket can be re-opened.

Actually, based upon your example, you may not even need the earliest record. How about the following:

SELECT
     identifier,
     activity,
     participant,
     closedate
    FROM
     performance a
    WHERE
     (a.identifier, a.closedate) in
      (select b.identifier, max(b.closedate)
       from performance b
       group by b.identifier
      )
;
Metro
In my case there are tons of intermediate activities ( well tens ) and close is definitely the last one. Using this query also brings uninteresting states, like closed and some others ( cancelled, rejected ) Thank you.
OscarRyz
Not to belabor this approach, but you can further qualify the where clause to select only the activities you are interested in such as: "AND activity IN (1,2,4)"
Metro
A: 

May be you can use this kind of query as a starting point.

select x.identifier, 
       max(x.p_1) as new_participant, max(x.c_1) as new_date,
       max(x.p_2) as inprogress_participant, max(x.c_2) as inprogress_date,
       max(x.p_3) as approval_participant, max(x.c_3) as approval_date,
       max(x.p_4) as closing_participant, max(x.c_4) as closing_date
  from (
        select a.identifier, 
               decode (activity, 1, participant, null) as p_1,  decode (activity, 1, closedate, null) as c_1,
               decode (activity, 2, participant, null) as p_2,  decode (activity, 2, closedate, null) as c_2,
               decode (activity, 3, participant, null) as p_3,  decode (activity, 3, closedate, null) as c_3,
               decode (activity, 4, participant, null) as p_4,  decode (activity, 4, closedate, null) as c_4
          from performance a
        ) x
 group by x.identifier

The idea is to serialize your table from row into field, and create a view based on it. You can create report based on this view.

Regards,

Salamander2007
A: 

Just a quick idea that others might build on (untested, but I hope the idea comes across):

First, select all not yet closed activities (as posted by others):

select id
from performance p1 where identifier not exists
  (select * from performance p2 where activity=4 and p1.id=p2.id)

Then, you can add the person attending the activity by adding a subquery in the select clause:

select id,
 (select participant 
  from performance p3 
  where p3.activity=3 and p1.id=p2.id)
from performance p1 where identifier not exists
  (select * from performance p2 where activity=4 and p1.id=p2.id)

If there is no activity 3 record for this id, the subquery returns null which is exactly what we need.

Hope this helps - please expand if necessary.

IronGoofy
+1  A: 

How about this:

SELECT * FROM (
  SELECT identifier,
         MAX(activity) activity,
         MAX(participant) KEEP (DENSE_RANK LAST ORDER BY activity)
    FROM performance
    GROUP BY identifier
)
WHERE activity in (1,2)

The inner query gives the latest activity for each ticket and its corresponding participant. The outer query filters this down to the ones where the activity is either "new" or "in progress".

I love the DENSE_RANK functions.

Dave Costa
In the history a close ticket has new and "inprogress" status too. So using where activity 1,2 will bring also a ticket with status 4. :( Actitivies have strange ids, not necessary sequential, I use 1,2,3,4 for clarity though. Thank you.
OscarRyz
What is DENSE_RANK?
OscarRyz
Hard to explain in the limited comment space. I suggest you check out the Oracle doc at http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions056.htm and post a new question if you want more explanation.
Dave Costa