Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Nivas
Starting Member
6 Posts |
 Posted - 2009-07-07 : 14:52:22
|
| Hello,I want to create a CURSOR for the below logic.... Plz suggest the code....A. The top part of the attachment is the input file layout. It has only 6 records: Account, Transaction date, Begin balance (in the first record), Transaction Type, CR or DR, Transaction Amount B. Business rules: If it is a deposit transaction, the running balance will increase. If it is other type of transaction (withdrawal), the running balance will decrease. C. If the running balance is negative (less than 0), the bank will charge the customer overdraft fee: $35 per transaction. D. When we sort the transaction differently, we will get different overdraft fee. Now, we have two sort-keys: The first one is based on transaction type (Deposit or ATM Cash, Fee..), the second one is based on Ascending or Descending transaction Amount E. If we have applied different Sort-order, it may come up with different total fee. ------------------------------------------------------------------ When the bank processes the transaction in different sequence, it will create different amount of Overdraft fee. In the attachment, I listed out 3 types of scenario:1. Sort sequence: a. Deposit first b. Cash withdrawal second c. Fee third d. All other transactions (Amount high to low) 2. Sort sequence: a. Deposit first b. Cash withdrawal second c. Fee third d. All other transactions (Amount low to high) 3. Sort sequence: a. Deposit first b. All other transactions (Amount high to low) |
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2009-07-07 : 18:05:48
|
Extremly likley that you do not need a cursor. Post some sample data and desired results.
Success is 10% Intelligence, 70% Determination, and 22% Stupidity.\_/ _/ _/\_/ _/\_/ _/ _/- 881 |
 |
|
|
Nivas
Starting Member
6 Posts |
Posted - 2009-07-07 : 18:34:42
|
| CASE:1"Begin Bal" ($50) "Tran Type" "CR(+)/DR(-)" "Tran Amount" "Running Bal" "Sort Order1" "Sort Order2" "FEE" "TOTAL FEE" DEPOSIT 1 10 60 1 0 0 ATM CASH -1 20 40 2 0 0 xxfee -1 10 30 3 0 0 Safeway -1 30 0 4 1 0 0 CHeCK -1 15 -15 4 2 35 CHECK -1 10 -25 4 3 35 70CASE2: "Begin Bal"($50)"Tran Type" "CR(+)/DR(-)" "Tran Amount" "Running Bal" "Sort Order1" "Sort Order2" "FEE" "TOTAL FEE" DEPOSIT 1 10 60 1 0 0 ATM CASH -1 20 40 2 0 0 xxfee -1 10 30 3 0 0 Safeway -1 10 20 4 1 0 0 CHeCK -1 15 5 4 2 0 CHECK -1 30 -25 4 3 35 35CASE3:"Begin Bal" ($50) "Tran Type" "CR(+)/DR(-)" "Tran Amount" "Running Bal" "Sort Order1" "Sort Order2" "FEE" "TOTAL FEE" DEPOSIT 1 10 60 1 0 0 Safeway -1 30 30 2 1 0 0 ATM CASH -1 20 10 2 2 0 0 Check -1 15 -5 2 3 35 0 CHeCK -1 10 -15 2 4 35 XXXFEE -1 10 -25 2 5 35 105 Please suggest the code....based on the above criteria....Thanks |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2009-07-07 : 19:03:10
|
| http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
|
|
|
|
|
Multiple scenarios using CURSOR