| Aggregate based on serial number after checking for thresholds [message #652830] |
Mon, 20 June 2016 12:08  |
 |
ketangarg86
Messages: 3
Registered: March 2015
|
Junior Member |
|
|
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).
[mod-edit: code tags added by bb]
[Updated on: Mon, 20 June 2016 15:59] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
| Re: Aggregate based on serial number after checking for thresholds [message #652835 is a reply to message #652830] |
Mon, 20 June 2016 13:06  |
 |
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
From your previous topics:
BlackSwan wrote on Thu, 12 March 2015 16:27Please read and follow the forum guidelines, to enable us to help you:
http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/
BlackSwan wrote on Thu, 16 April 2015 20:59http://www.orafaq.com/forum/mv/msg/196730/634654/#msg_634654
Michel Cadot wrote on Thu, 16 April 2015 21:54
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals.
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
In addition, feedback to your topics if not thank people to spend time to read your questions and help you.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
