Return Rows Only When a Ticket Status is the latest One

Question

Post reply

Return Rows Only When a Ticket Status is the latest One

gs1975

Ten Centuries

Points: 1057
More actions
August 30, 2019 at 9:22 am

#3675653

I am looking for a query that only returns rows where the latest ticket status is Awaiting Response.
I have looked at a number of different ways of doing it but I generally return more than the records I need.,
The following information should give an idea of what I require.
Ticket ID    StatusNo Status
1 1    Open
2    Awaiting Response
3 Closed

2    1    Open
2    Awaiting Response

In my results I would only want TicketID 2 returned and not TicketID 1 as TicketID2 has 'Awaiting Response' as the last status rather than any status.

Thanks in advance for any assistance.
Thanks,
George

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

gs1975 Ten Centuries Points: 1057 More actions · Answer 1

My above query got formatted differently to what I expected.

There are only two Ticket IDs (1 and 2)

The first ticket ID has 3 statuses (1 - Open, 2 - Awaiting Response ,-3 Closed). The second ticket ID has 2 statuses (1-Open and 2-Awaiting Response)

Thanks again,

George

gs1975 Ten Centuries Points: 1057 More actions · Answer 2

I have now solved this issue.

select ticketid, statusnumber, statusdesc

from

(

SELECT

ticketid, statusnumber, statusdesc

row_number() over (partition by ticketid order by statusnumber desc) as Seq

FROM

)t

Where Seq = 1

AND statusdesc IN ('Awaiting Response')

order by ticketid desc

Thanks,

George

nova SSC Journeyman Points: 80 More actions · Answer 3

Try this:

SELECT ticketid, MAX(statusno) AS statusno, MAX(status) AS status
FROM ticket
GROUP BY ticketid
HAVING MAX(statusno) = 2;

The presence of statusno and status in the same table looks like it might be creating a non-key dependency. If that's the case it is probably something you should fix, e.g. by removing the status column.

gs1975 Ten Centuries Points: 1057 More actions · Answer 4

Hi Nova,

Thanks for the script and your advice.

Much appreciated.

Kind regards,

George