SQL Transaction Type Check

Posted on 2011-05-05
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 59

    Accepted Solution


    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now