SQL Transaction Type Check

Posted on 2011-05-05
Medium Priority
Last Modified: 2012-05-11
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

Question by:Damozz
1 Comment
LVL 60

Accepted Solution

Kevin Cross earned 2000 total points
ID: 35700980

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.


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.

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question