SQL Help Needed

Domo · 16 Oct 2009 at 15:16

I have a table with the following data.

Ticket, Sequence, OldValue. NewValue, Date

1, 1, 'New', 'Closed', 07/01/2009 10:27:01
2, 2, 'New', 'In Progress', 10/01/2009 10:27:01
2, 3, 'In Progress', 'Assigned', 10/01/2009 11:45:45
2, 4, 'Assigned', 'Closed', 10/01/2009 13:32:56
3, 5, 'New', 'Assigned', 11/01/2009 08:22:23
4, 6, 'New', 'Assigned', 11/01/2009 15:44:24
4, 7, 'Assigned', 'In Progress', 12/01/2009 09:34:29
4, 8, 'In Progress', 'Resolved', 12/01/2009 13:59:49
4, 9, 'Resolved', 'Closed', 12/01/2009 14:01:22

I need to extract each record where the last entry for NewValue is 'Closed'.
Using the example above it would be tickets 1 (sequence#1), 2 (sequence#4) and 4 (sequence#9) - not 3 where the last entry is 'Assigned'.

I've tried a subquery using max which simply returns the very last record added, rather than all records where the criteria is met.

Help would be appreciated!

Smit · 16 Oct 2009 at 15:21

Assuming once a ticket is closed, it cannot be reopened (therefore is irrelevant if Closed is the newest sequence for the ticket or not):

SELECT ticket from xxx where NewValue = 'Closed'; should work? <--- Will return the ticket numbers where Closed is in the NewValue column

Edit - Maybe I misunderstood, you want all information for a ticket which has a state of closed?

Select * from xxx where Ticket in (select ticket from xxx where NewValue = 'Closed'); <--- Should find all tickets which have a closed state and then get all information for those tickets found

redeye_uk · 16 Oct 2009 at 15:43

Smit said:
Edit - Maybe I misunderstood, you want all information for a ticket which has a state of closed?

Select * from xxx where Ticket in (select ticket from xxx where NewValue = 'Closed'); <--- Should find all tickets which have a closed state and then get all information for those tickets found

Surely its even easier than that?

SELECT * FROM xxx WHERE NewValue = 'Closed'

i.e. I dont see a need for the nested select

Smit · 16 Oct 2009 at 15:45

redeye_uk said:
Surely its even easier than that?

SELECT * FROM xxx WHERE NewValue = 'Closed'

i.e. I dont see a need for the nested select

Because that will only return the lines where NewValue = Closed.

The OP isn't very clear what they want to be returned. My Second Query is to return ALL information for EVERY ticket which has at least one NewValue equal to Closed. In case they are needing to see the progress of each ticket before it was set to closed

OP says to extract each record.. I don't know if they mean each line or each ticket

Domo · 16 Oct 2009 at 15:53

All I really need is the ticket number which can then be linked to other tables.
Unfortunatley the tickets can be reopened.

redeye_uk · 16 Oct 2009 at 16:18

I see what you are trying to do. Surely the easiest solution is to add a new column 'LatestEntry' 1 ? 0

Smit · 16 Oct 2009 at 16:20

Not sure if this will work but give it a try (I named the DB test):

SELECT DISTINCT(ticket) from test where ticket in (select ticket from test where NewValue = 'Closed' AND sequence in (select MAX(Sequence) from test GROUP BY Ticket))

In theory it will only check the highest sequence number record for each ticket. This of course assumes that the sequence number will always be higher for new entries

Delboy · 16 Oct 2009 at 16:21

EDIT: Ignore.

aftershxck · 16 Oct 2009 at 16:32

redeye_uk said:
I see what you are trying to do. Surely the easiest solution is to add a new column 'LatestEntry' 1 ? 0

Easiest maybe, but surely the right solution (if possible) is to split out that horribly, horribly designed table. into Ticket/TicketStatus/TicketStatusValues or something along those lines .. ?

Smit said:
Not sure if this will work but give it a try (I named the DB test):

SELECT DISTINCT(ticket) from test where ticket in (select ticket from test where NewValue = 'Closed' AND sequence in (select MAX(Sequence) from test GROUP BY Ticket))

In theory it will only check the highest sequence number record for each ticket. This of course assumes that the sequence number will always be higher for new entries

@ OP - I tried the following and it worked.

select * from test t1 where newval='Closed' and not exists (select 1 from test t2 where t2.ticket_id = t1.ticket_id and t2.sequence > t1.sequence);

-- the subquery just making sure there isn't the same ticket_id with a higher sequence later on, so if you do re-open/close it should still work.

redeye_uk · 16 Oct 2009 at 16:35

aftershxck solution works, but surely its safer to check for the newest Date rather than highest Sequence number?

+1 for table redesign

Domo · 16 Oct 2009 at 16:46

Thanks guys, especially to aftershxck for your code which works. The sequence number is incremental so checking against it is fine.
I agree with all in terms of the table design but that's down to the CRM vendor not me!
Thanks again.
Dom

Yakyb · 16 Oct 2009 at 18:07

is the table filled by a trigger? would kind of make sense
i would use

select Z.ticket
from table inner join (
select ticket , max (sequence) as [max] from table as table_1 group by ticket)Z
on table.sequence = Z.[max]
where table.newvalue = 'closed'

run them both through SQL execution log thingy and see which is the quicker

edit: on this dataset looks like the first query is quicker as mine performs a sort before aggregation

edit 2: interestingly if you place an index (to remove the sort) on ticket asc and sequence desc my query becomes a little bit quicker