SQL conditionally return history.

Question

I have a table that has notes in it. It has a bunch of notes that automatically do different thing according to what someone does in the system.

When an order is assined it makes a note that says "Order was assigned to 'name'" When an order is re-assigned it makes a note that says "Order was re-assigned to 'name'"

When an order is assigned initially it throws the "Order was assigned to 'name'" statement into notes.

After that anytime the order is assigned again it throws the order was re-assigned.

The problem I have is that if the order is put into the system by the same person that assigns it to themselves it skips right to the reassigned note without even saying the order was assigned to.

Right now in my WHEN condition I have the following:

WHEN LEFT(n.Text,21) = 'Order was assigned to'

This basically returns only the name where the order was assinged the first time. Since if someone assigns an order to themself it skips this note, I need a statement that does this but if there is not 'Order was assigned to' I need to return the first order was re-assigned to note. These notes also have an audit insert timestamp field in the same table. An order can be re-assigned many times but I only want the first one if there is no Order was assigned to note.

How would I do this?

I know this is terrible application design but I didn't design it and I can't change it. I just need to know how to write the SQL.

THANKS!!!

Answer 1

You can take your current query, and add a UNION query with the same code and a slightly different where clause. The LEFT() would look for the re-assigned note, and you would add a NOT EXISTS clause to exclude orders with the proper note from the first query.