March 13, 2015 at 6:18 pm
Hello
I've got quite a head scratcher (at least for me).
What I have is a number of users. Each user can make 1 or more deposits. Those deposits are then used to pay 1 or more merchants.
The challenge is to connect deposits to payments. In some cases a single deposit can cover multiple payments. In some cases a single payment may need multiple deposits.
Here's a setup script to simulate the issue...
declare @UserDeposits table(DepositID int,
UserID int,
Amount decimal(19,2))
insert @UserDeposits
values(1, 100, 30.00)
,(2, 200, 40.00)
,(3, 200, 50.00)
,(4, 300, 60.00)
,(5, 300, 70.00)
,(6, 300, 80.00)
,(7, 400, 10.00)
,(8, 400, 10.00)
,(9, 400, 10.00)
declare @MerchantPayments table(UserID int,
MerchantID int,
Amount decimal(19,2))
insert @MerchantPayments
values(100, 1, 10.00)
,(100, 2, 10.00)
,(100, 3, 10.00)
,(200, 4, 20.00)
,(200, 5, 30.00)
,(200, 6, 40.00)
,(300, 7, 30.00)
,(300, 8, 40.00)
,(300, 9, 140.00)
,(400, 10, 30.00)
/* Possible Result
DepositIDMerchantIDAmount
1110.00
1210.00
1310.00
2420.00
2520.00
3510.00
3640.00
4730.00
4830.00
5810.00
5960.00
6980.00
71010.00
81010.00
91010.00
*/
In comments is 1 possible result. Depending on the order things are processed, it may be different. Which is OK if things still balance.
I've been playing with the quirky update idea, but I can't get it to work.
Any suggestions?
Thanks!
March 13, 2015 at 7:40 pm
Looks to me like you're missing something. Is there no "Account" table somewhere? If you think about a bank account, at its simplest, it's something like:
CREATE TABLE Account(
AccountNumber CHAR(16),
AccountOwner...
Balance SMALLMONEY
);
Then debits and credits are applied to the account, increasing and decreasing the balance when deposits and payments are made against the account. Then this is easy.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply