May 9, 2015 at 8:21 am
Good Morning, I've been trying to solve this issue for a few days now. Any assistance is greatly appreciated.
I have a total of $30 and need to deduct it from multiple deposits using the oldest deposits first and stopping at zero where the final deposit will not be fully depleted or used.
The dataset can be anywhere from 1 to 100 rows.
Here is the dataset.
CREATE TABLE Debits (ID INT, PlayerID INT, ExpireOnMonth DATE, Balance FLOAT, Amount_To_Debit FLOAT)
INSERT INTO [Debits]([ID],[PlayerID],[ExpireOnMonth],[Balance],[Amount_to_Debit])
VALUES(61485,178671,CAST('20150506' as DATE),CAST(14.1700 as MONEY),0)
INSERT INTO [Debits]([ID],[PlayerID],[ExpireOnMonth],[Balance],[Amount_to_Debit])
VALUES(402,178671,CAST('20151001' as DATE),CAST(9.6100 as MONEY),0)
INSERT INTO [Debits]([ID],[PlayerID],[ExpireOnMonth],[Balance],[Amount_to_Debit])
VALUES(61246,178671,CAST('20151201' as DATE),CAST(16.0700 as MONEY),0)
May 9, 2015 at 8:24 am
can you please provide create table statements for this data
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 9, 2015 at 8:28 am
Absolutely. I've updated what I posted. Thank you. 🙂
May 9, 2015 at 8:37 am
thanks for updating your code
now...you say you have been working on this for a few days....care to share what you have tried so far?
my initial thought is some form of running total.....have you tried that?
you have posted in SQL 2008 forum....is SQL 2008 what you are using or do you have SQL 2012/14?
you say "The dataset can be anywhere from 1 to 100 rows."...I assume this is per PlayerId and you wish to post one payment across all outstanding balances?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 9, 2015 at 8:54 am
Hi There,
This is what I have tried thus far to get to work consistently. With the dataset I posted, it works, but it can't be relied upon because on some datasets it fails.
DECLARE @Count INT,
@Expiration_ID INT,
@Expire FLOAT,
@Balance FLOAT
SELECT *, 'N' AS 'Processed' INTO #PE FROM Debits
SET @Balance = 30.00
SET @Count = (SELECT COUNT(*) FROM #PE WHERE Processed = 'N')
IF @Count > 0
BEGIN
WHILE (@Count > 0)
BEGIN
SET @Expiration_ID = (SELECT TOP 1 ID FROM #PE WHERE Processed = 'N' ORDER BY ExpireOnMonth ASC)
SET @Expire = (SELECT Balance FROM #PE WHERE ID = @Expiration_ID)
IF @Balance >= @Expire
BEGIN
UPDATE #PE SET Amount_To_Debit = @Expire WHERE ID = @Expiration_ID
SET @Balance = @Balance - @Expire
END
UPDATE #PE SET Processed = 'Y' WHERE ID = @Expiration_ID
SET @Count = @Count - 1
END
END
IF @Balance < @Expire
BEGIN
UPDATE #PE SET Amount_To_Debit = @Balance WHERE ID = @Expiration_ID
SET @Balance = @Balance - @Balance
END
SELECT @Balance
SELECT * FROM #PE
May 9, 2015 at 8:57 am
If the first figure is small it will then fail and I just haven't been able to figure out the correct logic to fix it. Well, I may be wrapped around the wheel at this point.
Run this and then the script and it will fail all over the place.
UPDATE Debits SET Balance = 4.00 WHERE ID = 61485
May 10, 2015 at 6:59 am
again assuming you are on SQL 2008....search this site for Jeff Moden and his articles and code for "Quirky update"....this may help you. There are some absolute rules to follow ...but in my experience it is fast and accurate
http://www.sqlservercentral.com/articles/T-SQL/68467/
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply