Analytical functions in db2

Posted on 2014-08-26
Last Modified: 2014-09-12
A table has accountnumbers, status codes and effective dates. I need to get accountnumbers, status codes and effdates which has statuscode = ' 5' and also statuscode ='F' or statuscode = 's' before having statuscode = '5'. These account numbers should have had statuscode= 'F' or statuscode = 's' before statuscode = '5'.

What is the best way to write the query. Please suggest
Question by:d27m11y
    LVL 37

    Accepted Solution

    Select account number, effdate
    From table1 p
    Where status code='5'
    And exists (select 1 from table1
       Where accountnumber = p.account number and effdate < p.effdate and status code in ('f', 's'))
    LVL 26

    Expert Comment

    It makes no sense to say "which has statuscode = ' 5' and also statuscode ='F'" nor to say "statuscode = 's' before having statuscode = '5'". Much more detail is needed.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
    Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    760 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

    13 Experts available now in Live!

    Get 1:1 Help Now