Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2009
    Posts
    62

    Unanswered: select record based on max date

    Hi,

    I have the following table:

    Occ_Num Feature_Num Trans_Date Peril_Desc
    123 1 1-2-2012 Water
    123 1 1-11-2012 Ice
    123 2 1-2-2012 Other
    123 2 1-13-2012 Other
    123 2 1-19-2012 Wind

    I want to select each Occ_Num, Feature_NUM, Trans_Date, and PERIL_Desc but with only the Peril that was part of the max trans_date.

    So i would want the following from above:

    Occ_Num Feature_Num Trans_Date Peril_Desc
    123 1 1-11-2012 Ice
    123 2 1-19-2012 Wind

    I'm having trouble with the syntax need to accomplish this.

  2. #2
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    You have been here a while - you should use the Code tag when posting data that needs to be aligned to be easily read.

    Please explain why you want the 2 rows you show as the desired output.

    Clearly explaining the rules for selectoin will help someone help you.

  3. #3
    Join Date
    Sep 2009
    Posts
    62
    These are insurance claims that are entered via a front end system. The issue is that as they are adding more transactions to each feature of a claim occurrence, they change the Peril, which they should not do.

    I know this should be fixed in the front end, but for now we have to use what we have stored in the database. When we try to aggregate the data to the feature level, we need to only get 1 row for each combination of occurrence and feature with the Peril. But because their are 2 perils for the given occurrence/feature combination, we get multiple rows.

    So our logic is to only use the peril that was entered during the last transaction of the occurrence/feature combination.

    Hope i'm explaining that ok.

Posting Permissions

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