Unanswered: Aggregate based on serial number after checking for thresholds
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).
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
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.