Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2012
    Posts
    31

    Unanswered: MAX on date field acts differently on a table

    Hello-

    I am trying to pull in provider numbers with their latest tax ID, based upon the D_TAX_EFF field.

    I had a similar situation with another table where I needed the most recent description for a code based upon the D_DRG_CANCEL field.
    This code worked beautifully on that table:
    SELECT * FROM DB2PROD.CRPTDRGCAT
    Where D_DRG_CANCEL=
    (SELECT MAX(D_DRG_CANCEL) FROM DB2PROD.CRPTDRGCAT)
    resulting in only the rows with the most current cancellation date.

    But when I run this:
    SELECT C_PLAN, I_PROVIDER_NUMBER, I_TAX
    FROM DB2PROD.OPRTPRVTAX
    WHERE D_TAX_EFF =
    (SELECT MAX(D_TAX_EFF) FROM DB2PROD.OPRTPRVTAX )

    I only get back the provider number with the most current cancellation date, so only one row! And when I specify a particular provider number, (one I know has more than one tax ID), I get NOTHING because it isn't the provider with the very most recent D_TAX_EFF value.

    The purpose of this is to get rid of duplicates and only pull back the most current tax id for a provider.
    The structure of the date fields on the two tables is identical.
    What am I missing? What do I need to do differently?

    Trying to pare down a query result from over 200,000 rows by getting rid of duplicate providers due to the multiple tax IDs.

    Thanks


    Laura

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What are your DB2 version/release/fixpack and platform OS?

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    (SELECT MAX(D_TAX_EFF) FROM DB2PROD.OPRTPRVTAX )
    The subquery reurns one MAX(D_TAX_EFF) from the table, regardless I_PROVIDER_NUMBER, as you described.

    You need to find MAX(D_TAX_EFF) for each I_PROVIDER_NUMBER.
    Right?
    So, at first, get all I_PROVIDER_NUMBER with corresponding MAX(D_TAX_EFF) in the subquery.
    Then, use like this condiion ...
    Code:
     WHERE (I_PROVIDER_NUMBER , D_TAX_EFF)
           IN (revised subquery)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •