CODEWITHSUNDEEP
mysqlif-statement
user1028445
user1028445

Reputation: 33

mysql query based on ticket status

I'm working on a ticketing system where analysts will be assigned tickets based on a specific criteria.

When they first log in they will click on the Get Net Ticket button.

The tickets are assigned as follows:

  1. First: Open (oldest ticket with status of Open)
  2. Second:Pending (any tickets with Pending status when pend date = today)
  3. Third: Ready (oldest, new (unassigned) ticket)

So if they click Get Next it will look for oldest open, if none > select Pending today, if none > select oldest Ready.

Below is my query but it doesnt seem to be working reliably. Any suggestions?

    SELECT * 
    FROM support_case
    WHERE (
    case_status = 'Pending'
    AND case_queue = 'Software'
    AND pendDate = '12/15/2011')
    OR (
    case_status = 'Open'
    AND case_queue = 'Software')
    OR (
    case_status = 'Ready'
    AND case_queue = 'Software')
    ORDER BY lastUpdate ASC 
    LIMIT 0 , 1

Upvotes: 0

Views: 613

Answers (1)

Patrick Moore
Patrick Moore

Reputation: 13354

This will sort them by support_case, then by oldest open.

Open will be sorted oldest->newest, followed by Pending sorted oldest->newest, then Ready sorted oldest->newest.

SELECT * 
FROM support_case
WHERE ( 
case_status = 'Pending' 
OR case_status = 'Open' 
OR case_status = 'Ready' )
AND case_queue = 'Software'
ORDER BY support_case ASC, lastUpdate ASC
LIMIT 1

If you had different status values (that weren't nicely alphabetical), you could restructure your database, or add a new column, to give a numerical value to your case_status. Then you can add an ORDER BY case_status_value DESC to your query.

So: 'Open' = 10 'Pending' = 5 'Ready' = 1

So that Open are returned first, then pending, then ready. Then you can query:

SELECT * 
FROM support_case
WHERE ( 
case_status = 'Pending' 
OR case_status = 'Open' 
OR case_status = 'Ready' )
AND case_queue = 'Software'
ORDER BY case_status_value DESC,
lastUpdate ASC
LIMIT 1

See @konerak's comments below for another option that accomplishes the same, without the column structure changes.

Upvotes: 1

Related Questions