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).
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.
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);
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
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.
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
)
;
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,
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.
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.