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 |
|
sherrer
64 Posts |
 Posted - 2003-01-06 : 10:59:35
|
| I am trying to come up with a way not to use a cursor in an application that we are developing. We have 3 tables that get affected during a posting routine; currently we are posting account by account from a VB application. It is a long and painful routine that has to keep table locks on the tables involved for integrity reasons. The tables are as follows; I am only giving the columns involved in the procedure.PmtAdj PmtAdjID Uniqueidentifier – PK PmtAdjAmt Money DepletedAmt MoneyPmtAdjPosting PmtAdjID Uniqueidentifier – PK TransID Uniqueidentifier – PK AppliedAmt MoneyBillHist TransID Uniqueidentifier – PK AcctID Uniqueidentifier – FK TransDt DateTime TransAmt Money OpenBal Money RevCode char(4)PmtAcctLink (Used due to the fact that payments can span multiple accounts) AcctID Uniqueidentifier – PK PmtAdjID Uniqueidentifier - PKI want the posting routine to be as efficient as possible, so my goal is to post all payments and adjustments at one time. Payments and adjustments are written to the PmtAdj table and initially have a depleted amount of 0 and is updated during posting to show how much of the payment or adjustment has been posted to a transaction. The act of posting a payment or adjustment (credit) links it to a transaction (charge) in the BillHist table. Our normally order of posting, posts credits to charges in order of oldest first then by revcode. The revcode posting order is determined elsewhere in the database. When a credit is applied to a charge, a row is inserted into the PmtAdjPosting showing which credit applied to which transaction and the amount that was applied. Also, the OpenBal column is update to show how much of the charge still exists.For example a $25 payment can payoff a $5 and $20 charge. The depleted amount on the payment in the end would be $25 and the open balances on the charges go to $0 and two records would be added to PmtAdjPosting. If the payment were only $20, and the $5 charge had a higher order, then the $5 charge would have an open balance of 0 and the $25 charge would have an open balance of $5. And if the payment was $30, both charges would be paid off in full and a remaining credit would be $5, indicated by the depleted amount for the payment being $25. (Some simplified rules: A charge is fully paid when openbal reaches 0 from the original charge amount. A payment is full used when the depleted amount reaches the absolute value of the original payment starting at 0. Payments can span several charges and charges can be paid by multiple payments.)I hope this long-winded explanation is good enough for some advise. I don’t have any problems with updating the PmtAdj table or inserting into the PmtAdjPosting table. My real need is to figure out how to apply payments across different charges in an ordered fashion without using a cursor, keeping track of how much was applied and updating the openbal column in BillHist. Thanks Kevin...Edited by - sherrer on 01/06/2003 11:00:16 |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2003-01-06 : 11:19:03
|
| If you could construct a running total of the charges, you could probably see the solution right away.Jonathan{0} |
 |
|
|
sherrer
64 Posts |
Posted - 2003-01-06 : 11:22:57
|
| And of course constructing a running total is most efficiently done using a cursor. I am looking for an alternative method... if there is one.Thanks |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2003-01-06 : 11:41:59
|
Not quite my point. Once you did have a running total, however obtained, you could more easily apply set based methods to matching payments with charges, by finding the min( runningtotal - payment ).Computing the running total doesn't have to be done with cursors, either. Take the following:create table #temp( payerID int, seq int, amount money )insert #temp( payerID, seq, amount ) select 1, 1, 5.00 union select 1, 2, 6.00 union select 2, 1, 4.00 union select 3, 1, 3.00 union select 3, 2, 2.00 union select 3, 3, 1.00 union select 3, 4, 7.000select payerID, seq, amount, (select sum(amount) from #temp where payerID = t.payerID and seq <= t.seq) as running_totalfrom #temp t Jonathan{0} |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2003-01-06 : 13:26:38
|
| {tumbleweeds ... the whoosh of abandoned ideas ...}Jonathan{0} |
|
|
|
sherrer
64 Posts |
Posted - 2003-01-06 : 13:52:53
|
| Ok, forget the cursor for the running total... although it is much faster than doing a sub-query to total. There are a couple of good discussions about it on this site.I do not understand where you are going though. If you have a good thought and the time, I would like to hear it out. Here is some data that would be a small example of what I have. I will be more typically dealing with 4k to 5k accounts on a daily basis.Let’s assume that the order of the charges is the order they get paid by the paymentAccount ACharge1 $5 OpenBal $1Charge2 $10 OpenBal $10Charge3 $1 OpenBal $1Charge4 $22 OpenBal $22Payment1 $15 Depleted $5Payment2 $30 Depleted $0Account BCharge1 $10 OpenBal $6Charge2 $20 OpenBal $20Payment1 $20 Depleted $0Account APayment1 has $10 left to apply to charges. The first charge has $1 left to pay off, so $1 goes to Charge1 and is written to the PmtAdjPosting table. The depleted amount of Payment1 is now $6. Charge2 takes the rest of Payment1, $9. Payment1 is now fully depleted at $15 and openbal on Charge2 is $1. A row is added to PmtAdjPosting table. Payment2 now applies to the $1 openbal on Charge2 which makes the depleted amount $1 and the openbal $0. A row is written to PmtAdjPosting. Payment2 now applies $1 to Charge3 which makes the depleted amount $2 and the openbal of Charge2 $0. A row is written… Payment2 applies $22 to Charge4 making openbal $0 and depleted amount $24. And a row is written… The account now stands with a $6 credit. (I hope all my math was correct)Account B has to be processes by the same method. In this case Payment1 would end up fully depleted and Charge2 would have an openbal of $6. Two rows were written to PmtAdjPosting.I am starting to see a way to do this using a total of payments and charges at an account level. Let’s take one scenario. I would find all accounts that end up as a credit and can update all the bill history since all openbal go to 0. Then here comes the problem, I have to write a record for each payment and the corresponding transaction which was paid by the credit. Also, I have to find out if there is a payment that was partially depleted and update it accordingly. To do this I have to break it back down to the actual payment level so that the payments correctly apply to the charges in the proper order.Does this make since? If so, you could quickly outline you thoughts. Don’t worry about writing code; I just need to know what you are thinking.Edited by - sherrer on 01/06/2003 13:56:11Edited by - sherrer on 01/06/2003 13:56:58 |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2003-01-06 : 14:25:05
|
| If your current process is a cursor-based mapping of each individual payment to each individual charge, my suggestion is to precompute a running total b/c that will allow you to match a group of charges at once to a single payment, probably by using a while loop.Your requirements for updating both the charge and the payment row with stateful information invalidates a set-based solution. I don't understand the utility of knowing anything more than the aggregate balance of the account.Jonathan{0} |
|
|
|
sherrer
64 Posts |
Posted - 2003-01-06 : 19:08:27
|
| Thanks Jonathan for your insight. I think I have come to the same conclusion as you. I don't see a complete way to get around a cursor or a loop, but I now see some things that can be done in a more set-based approach. I think I can improve our performance over what is being done in VB that is not only account based, but the payments and charges have to be looped through as well.As for the fact that we need to know what payments have been applied to charges and in an ordered fashion... The application rules require it, first of all. For instance, I bill someone's rent, cable, and water utility. It is unlawful to not pay your rent, but not necessarily so for utilities and services. If someone doesn't pay their bill in full, and the charges are applied first to utilities and services, there is a eviction case if so persued if funds are missing for the rent portion. Also, there is a need to break down charges into revenue streams. If it is more important to collect for utility charges rather than services, then you must be able to at any time tell which revenue stream incoming funds have been booked.Kevin |
|
|
|
|
|
|
|
|
Trying to avoid a cursor!!!