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 |
|
jamie123
Starting Member
15 Posts |
 Posted - 2008-07-15 : 13:24:39
|
| I am using sql express 2005 programming in vs2008 with vb.net I have an sql database and am programming a databasing financial application. In this application, it displays a datagridview that consists of entries that are located on the sql database, the entries are charges. You can make payments on these charges on each of the lines. The problem I am having is I have a box that calculates "Todays Payments" ..it's a stored procedure that sums all payments in the "Payments" column of my database where the column "Dateofpayment" = current date. This makes sense, but you can make payments in this program in increments. For example, let's say someone was charged for 40 dollars. On the day I was charged, I make a payment for 10 dollars. Today's Payment displays 10 dollars. Tomorrow, I start up the program, Today's Payment is equal to 0. I make a payment on the 40 dollar charge that incurred yesterday, 10 dollars again. The balance on the line item updates correctly but the Today's Payment shows 20 dollars, instead of 10 dollars..which is what was actually paid today. This is because the stored procedure sums everything in the payment column that has a Dateofpayment = current date, since the date of payment was updated when I made a new payment, it now makes sense why this error is occuring...But I really dont' know how I can fix it. Since there is only one column for payment, there's no way to distinguish (in the current database structure) if partial amounts of the payment were paid on different days, and whether or not the whole payment should be included in Today's Payments. Does anyone have any ideas on what I could do to have this calculate correclty? Any help would be greatly appreciated. Thanks! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-15 : 13:29:02
|
| You should be atleast having a history/audit table if you want to capture the incremetal payments. For each updation happening on main table payment column, you need to populate a history table with payment amount till that day (before updation) using an update trigger. so that the payment for day can be captured by diffrent mainpaymentfieldvalue-historypaymentfieldvalue for a particular date. |
 |
|
|
jamie123
Starting Member
15 Posts |
Posted - 2008-07-15 : 17:30:10
|
| Thank you, I completely forgot that I actually do have such a table. I just ended up executing the stored procedure using that table that recorded each payment by payment by a particular date, thanks! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-15 : 23:54:58
|
quote:
Originally posted by jamie123
Thank you, I completely forgot that I actually do have such a table. I just ended up executing the stored procedure using that table that recorded each payment by payment by a particular date, thanks!
You're welcome  . Feel free to ask whevever you've a doubt |
|
|
|
|
|
|
|
|
stuck in a situation