SQL Transaction Type Check

I am trying to produce a report showing certain transactions allocated to a particular incident.  In one particular case I need to check if a particular incident has one of two specific transaction types, if it does then it should use one of them, but never both. Is there a clean way of doing this.  The only way I can think of is using subqueries/table aliases and coalesce to get the result.  I would still need the other transaction types included if they had been entered.


Many thanks

Damian
DamozzAsked:
Who is Participating?
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
Damozz,

You can try with a NOT EXISTS clause.  If I understand correctly, you want to select all rows for a particular incident, but exclude one of a mutually exclusive transaction type.  In order for it to work, you will probably need to hook on to another field to use as tie breaker.  Say a datetime field or a sequential identifier and always use the newer/older record as trump.

i.e.,

select {column list}
from your_table a
where incident_id = 'xyz'
and not exists (
   select 1
   from your_table b
   where b.incident_type in ('x', 'y') -- the two types that are mutually exclusive
   -- this combination should filter out the type with older date
   and b.incident_type <> a.incident_type and b.incident_date > a.incident_date
)

Unless you can have collisions on the same exact timestamp then this should work.  If you can, then a sequential id would work better.  If you don't have one, you might be able to get creative using something like ROW_NUMBER analytical function where you partition by the type but using case statements you associate the mutually exclusive types as if they are one for partitioning purposes -- then you can just pick only row numbers = 1 for example.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.