| filter invalid amount records [message #332166] |
Mon, 07 July 2008 14:10  |
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
|
Senior Member |
|
|
I have these 2 tables ...
table1
loan_id acct_nbr exp_acct amount
123 a1234 8765 50.00
156 a3245 9809 100.00
876 a7564 0975 500.00
table2
loan_id amount
123 10.00
123 10.00
123 10.00
123 10.00
123 10.00
123 140.00
876 600.00
876 200.00
876 300.00
The table2 is the detail table of table1, but containing some records of invalid amount. i need to select detail records from table2 based on this rule...
Like loan_id = 876 containing 3 amounts 600 , 200 and 300. so i need to select that loan id, if i add their amount which should be equal to the amount of table1 of same loan_id.
The query result from table2 should be Something like this..
loan_id amount
123 10.00
123 10.00
123 10.00
123 10.00
123 10.00
876 200.00
876 300.00
I am not looking for detail answer. any clue or hint would be appreciated..
|
|
|
|
|
|
| Re: filter invalid amount records [message #332173 is a reply to message #332171] |
Mon, 07 July 2008 14:27  |
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
|
Senior Member |
|
|
>>'And what happens if instead of 600 you had 500?'
If the amount is also exactly matched with the loan_id .. that is ideal situation.. but if not, then take the sum of amount and match against the table1 of the same loan_id..
>>And what about loan_id 156?
I am selecting only those loan_id's from table2 which exists in table1 having the same amount or end up getting the amount matched, after adding them....
Appreciate your valuable time ...
|
|
|
|
|
|
| Re: filter invalid amount records [message #332179 is a reply to message #332175] |
Mon, 07 July 2008 14:50 |
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
|
Senior Member |
|
|
Ok here is some test cases...
select only these records from table2
1)Firstly, if the loan_id and the amount of the table2 matches with the loan_id and amount of table1. (ideal situation)
2) secondly, if the only loan_id of the table2 matched against table1, then sum the amount of same loan_id and try to make sure it matches the amount of table1. but if there is more than one match, then pickup first match only.
|
|
|
|
|
|
|
|
| Re: filter invalid amount records [message #332734 is a reply to message #332255] |
Wed, 09 July 2008 09:02 |
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
|
Senior Member |
|
|
I do apologize for unable in explaining my problem, and waisting your valuable time. I am trying once more to explain my problem..
table1
loan_nbr amount
123 100.00
1234 130.00
table2
loan_nbr amount
123 25.00
123 25.00
123 25.00
123 25.00
123 1234.43
please just give me a small hint, to select only those loan_nbr from table2 whose sum amount is exactly equal to the matching loan_nbr of table1. i am just looking any hint to create a logic only for this situtaion, which i tried to mentioned above in the table..
Thanks and appreciate your time.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
