July 3, 2008 at 8:39 am
I work for a nonprofit agency which funds services performed by other agencies, who enter the services they rendered on our ASP.NET website. I have noticed that there appears to be a discrepancy in the tracking of the funds being spent, and I don't understand why that should be, because all of the transactions occur within a database transaction.
Basically, we've got a situation in which we've got a table of funding sources. This table is called MoneyPools and it has a couple of columns, one called AnnualAllocation, which is the amount of money assigned to that fund each fiscal year, and RemainingValue, which is the money still in the pool after services have been rendered by the external agencies.
The external agencies are issued work agreements to perform services, and the individual work agreements are called vouchers, so we've got a Vouchers table. Each record in the Vouchers table has a couple of columns, one named VoucherCap, which is the amount assigned to the voucher when it is created, and AmountExpended, which tracks how much money is spent against the voucher. (There is a table constraint in place which will not allow the AmountExpended to exceed the VoucherCap- that's not relevant to my question, but it is an interesting fact nevertheless.)
Lastly we've got a table which tracks the services rendered by the external agencies; I'll call this table Services. For the purposes of my question, I'll leave out the details about how the Services table is related to the other two tables; it is, trust me. What is relevant to my question is that the Services table has 2 columns in it, which are the Amount column for the amount of the service and the GrossReceiptsTax column which of course is the gross receipts tax for the given amount.
What happens is this. The service rendered by the external agency is calculated and entered into the Services table, into the Amount and GrossReceiptsTax columns. The sum of those two columns is added together and subtracted from the AmountExpended column in the Vouchers table, and it is also subtracted from the relevant fund in the MoneyPools table's RemainingValue column. This all happens within a database transaction so that if at any point, if the service entered by the external agency should exceed the funds available for that voucher, or the funds available for the money pool, then the whole transaction is rolled back. Otherwise it goes through.
I would therefore think that, if I were to add all of the services (the sum of the Amount and GrossReceiptsTax columns from the Services table) over the fiscal year, then I would expect that to be equal to the difference between the AnnualAllocation minus the RemainingValue columns of the MoneyPools table. And yet, it isn't.
Why not?
Kindest Regards, Rod Connect with me on LinkedIn.
July 3, 2008 at 9:12 am
How far is it off? Hundreds & thousands or only a few dollars or cents? If it's the smaller number, it could be rounding errors. You didn't specify the data types in all the tables & columns described above. If some are smaller than others, the data will be rounded to the smaller, less accurate, data types.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 3, 2008 at 9:29 am
You're right, I did forget to mention the data types. In the MoneyPools table both the AnnualAllocation and the RemainingValue columns are of type money.
In the Vouchers table the VoucherCap and AmountExpended columns are both money.
In the Services table, the Amount column is of type money, but the GrossReceiptsTax column is of type smallmoney.
The difference is a few thousand dollars.
And another thing I wanted to mention is that for a fiscal year, there can be between 90,000 and 100,000 service records entered in the Services table.
Kindest Regards, Rod Connect with me on LinkedIn.
July 3, 2008 at 9:42 am
Then that doesn't sound like a structural problem. It must be one of logic.
There's no way for innacurate numbers to be entered or values to be changed over time?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 3, 2008 at 11:46 am
Rod at work (7/3/2008)
You're right, I did forget to mention the data types. In the MoneyPools table both the AnnualAllocation and the RemainingValue columns are of type money.In the Vouchers table the VoucherCap and AmountExpended columns are both money.
In the Services table, the Amount column is of type money, but the GrossReceiptsTax column is of type smallmoney.
The difference is a few thousand dollars.
And another thing I wanted to mention is that for a fiscal year, there can be between 90,000 and 100,000 service records entered in the Services table.
You ought to be able to isolate you issue a little more. You have the ability to audit this voucher by voucher, so you should be able to see if ALL of them are off by a little (some kind of rounding issue/loss of precision), or if the discrepancy is isolated to a few vouchers (probably related to some activity not posting correctly, such as a receipt posting, but the reflected activity didn't update the aggregate column, or updated it twice....)
Also - does this trigger happen EVERY time an update is issued, or only when the amount column changes? If you're not checking that, you could easily "double-post" the effect of a given receipt, by for example updating a date column in the receipts table after you updated the receipt amount.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 3, 2008 at 12:08 pm
Are the transactions being tracked by a trigger? If so, is it set up correctly to deal with multi-row inserts/updates?
Is it possible to delete a voucher? If so, does that put money back into the remaining balance?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 3, 2008 at 12:12 pm
Not to mention, is there more than one access path? Can people modify the values after they've been entered? If so, are there triggers in place to account for that as well?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 3, 2008 at 1:51 pm
OK, this is very weird, but I just re-ran my query again, summing all of the Amounts and GrossReceiptsTax added together, against the fund, and it came out exactly to the penny to what it should be.
All I can say is probably I specified the date range wrong when I did this the first time.
I am very sorry to have bothered you all with this problem that turns out to be a non-issue.
Kindest Regards, Rod Connect with me on LinkedIn.
July 3, 2008 at 1:57 pm
Though I didn't respond to your original post, I assure you, not a problem. Sometimes asking for help is all it takes. In asking for help, you had to step away from the forest to see the trees.
Been there, done that, just haven't bought the t-shirt (I keep going back).
😎
July 3, 2008 at 2:07 pm
Lynn Pettis (7/3/2008)
Though I didn't respond to your original post, I assure you, not a problem. Sometimes asking for help is all it takes. In asking for help, you had to step away from the forest to see the trees.Been there, done that, just haven't bought the t-shirt (I keep going back).
😎
Yeah, I know what you mean. I've got a closet full of those t-shirts.
Kindest Regards, Rod Connect with me on LinkedIn.
July 3, 2008 at 7:54 pm
Of all the problems you could possibly have - picking the wrong date range on the the verification is the easiest one to fix....:D All I can say is - happens to all of us at some point or another....
Glad it didn't turn into any number of painful "real" problems....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 3, 2008 at 9:59 pm
Heh... yeaup... we've all been there.
My problem is that I end up double and triple checking my query, find out that it's spot on, and that someone really did screw the data up! :hehe:
--Jeff Moden
Change is inevitable... Change for the better is not.
July 7, 2008 at 5:27 am
May as well pile on to this one...
It's not a problem. Half the time when I type up the question to post it up here when I'm stuck, the act of gathering the information so I ask a coherent and complete question leads me to the correct answer.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply