Results 1 to 3 of 3
  1. #1
    Join Date
    May 2015
    Posts
    3

    Unanswered: Aggregate based on serial number after checking for thresholds

    Hi Guys,

    I am relatively new to SQL oracle and need your help.

    I have a table which has the following columns
    Type, Serial Number, Principal, Loan Amount and Date (sample below)

    Type Serial Number Principal Loan Amount Date
    TEST 12345 17.58704 18 June 20th
    TEST 12345 18.1 June 20th
    TEST 12345 180 June 20th
    TEST 12345 18 June 20th
    TEST 12345 17.587 18 June 20th
    TEST 12345 17.9 June 21st
    TEST 12345 18 June 21st
    TEST 12345 18 June 21st


    Now I want to aggregate principal and loan amount based on serial number and type for any given day. Serial number and type relationship is one to one.

    A few issues, I am having
    1. I dont want to double count the principal or loan amount
    2. To account for typo entries in data entry, I want to exclude the entry where there is a factor of more than 10 (example loan amount of 180 in above sample data). Now this exclusion is based on the majority of the rows falling in the same range on a particular date. If there is only one row for a unique serial number it is fine. If there are two, then exclude both if there is a factor of more than 10. If there are three, then take the majority.
    3. So if the principal for a particular serial number/type combination on a given day is under 10% threshold of each other, I would want to take an average of them and treat as one. Same goes for the loan amount.
    4. For point 3, if the threshold is more than 10% then aggregate the data (this is under the assumption that step 2 will already clean up the data).

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    I don't see enough data for the columns you say your have.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    I dont want to double count the principal or loan amount
    There's absolutely no chance of that happening - none of your examples show a loan amount (or maybe they don't show a principal. I'm not sure which one is missing as your table isn't formatted - read the sticky at the top of the forum page for advice on formatting).

    this exclusion is based on the majority of the rows falling in the same range
    What range???

    If there is only one row
    Do you mean row? Or record? A record can be split over more than one row.

    If there is only one row for a unique serial number it is fine
    I can see 8 records all with the same serial number. That's not 'unique'. Do you mean "If there is only one record for a serial number it is fine"? (Or is '12345' the principal and it's actually the serial number that's missing?)
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

Posting Permissions

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