Need Help Updating a Table with Amount Limits per Group

Transact-SQL
1

I have a table named Payments that contains the following:

ID AcctNo RevCode Amount Group GroupMaxAmt
7 001 360 1000.00 1 3000.00
1 001 361 1500.00 1 3000.00
3 001 362 1600.00 1 3000.00
4 001 710 1500.00 2 3400.00
6 001 711 2000.00 2 3400.00
2 001 712 2100.00 2 3400.00
9 002 360 1600.00 1 3000.00
8 002 361 1650.00 1 3000.00
5 002 490 2550.00 3 2500.00
10 002 491 1000.00 3 2500.00

RevCode 360 to 362 belong to Group 1 for which the maximum amount is 3000.00 for the sum of Amount in the Group falling under each AcctNo. So, for example, AcctNo 001 has RevCode 360 to 362 for which the sum of Amount is 4100.00 which exceeds the maximum amount of 3000.00 for Group 1. I need to update the table so that the sum does not exceed the maximum amount for the Group. The update should reduce the Amount starting with the highest amount until the sum no longer exceeds the maximum amount for the Group.

Here is how the table should look like after the updates are done:

ID AcctNo RevCode Amount Group GroupMaxAmt
7 001 360 1000.00 1 3000.00
1 001 361 1500.00 1 3000.00
3 001 362 500.00 1 3000.00
4 001 710 1500.00 2 3400.00
6 001 711 1900.00 2 3400.00
2 001 712 0.00 2 3400.00
9 002 360 1600.00 1 3000.00
8 002 361 1400.00 1 3000.00
5 002 490 2500.00 3 2500.00
10 002 491 0.00 3 2500.00

Could someone please help me with the SQL script to perform this update? Ideally, the script should not have to use a cursor.

Thank you so much in advance! It would be much appreciated.

BTW, how do I enter a table in a posting so that the spaces I enter between columns are preserved? The table I entered above is hardly legible. :slight_smile: Sorry about that.

2

The most difficult and time-consuming part for most of the forum regulars who want to respond to your question is creating a test data set. If you provide the DDL statements to do that, you will get much faster responses. What I mean is that you should post something like this that people can copy and run:

CREATE TABLE #tmp( Acct INT, revcode int, Amt FLOAT, Grp INT, GrpMaxAmnt FLOAT);
INSERT INTO #tmp VALUES 
(1,360,1000,1,3000), (1,361,1500,1,3000),(1,362,1600,1,3000),
(1,710,1500,2,3400),(1,711,2000,2,3400),(1,712,2100,2,3400),
(2,360,1600,1,3000),(2,361,1650,1,3000);

SELECT * FROM #tmp;

Once you do that, the query can be written, for example, like this. The following assumes that you are on SQL 2012 or later.

;with cte as
(
	select
		*,
		sum(Amt) over (partition by Acct, Grp order by revcode) as RunningTotal
	from
		#tmp
)
update cte set 
	Amt = 
		case 
			when RunningTotal <= GrpMaxAmnt then Amt
			when RunningTotal > GrpMaxAmnt 
				and RunningTotal - GrpMaxAmnt > Amt then 0
			else
				Amt - (RunningTotal - GrpMaxAmnt)
		end;


select * from #tmp order by acct, revcode;
3

Thank you so much, James!

I'm on SQL Server 2008, though, and the script is giving me an error. Could you please provide the script for 2008?

Here's a more accurate representation of the data:

CREATE TABLE #tmp(ID Int, AcctNo varchar(3), RevCode int, Amount Money, GroupNo INT, GroupMaxAmt Money);
INSERT INTO #tmp VALUES
(7,'001',360,1000,1,3000),(1,'001',361,1500,1,3000),(3,'001',362,1600,1,3000),
(4,'001',710,1500,2,3400),(6,'001',711,2000,2,3400),(2,'001',712,2100,2,3400),
(9,'002',360,1600,1,3000),(8,'002',361,1650,1,3000),
(5,'002',490,2550,3,2500),(10,'002',491,1000,3,2500);

And here's the data after the table is updated:

CREATE TABLE #Utmp(ID Int, AcctNo varchar(3), RevCode int, Amount Money, GroupNo INT, GroupMaxAmt Money);
INSERT INTO #Utmp VALUES
(7,'001',360,1000,1,3000),(1,'001',361,1500,1,3000),(3,'001',362,500,1,3000),
(4,'001',710,1500,2,3400),(6,'001',711,1900,2,3400),(2,'001',712,0,2,3400),
(9,'002',360,1600,1,3000),(8,'002',361,1400,1,3000),
(5,'002',490,2500,3,2500),(10,'002',491,0,3,2500)

Please note: When updating the Amount, it should update from highest amount to lowest. For a good example of this, please see the rows for GroupNo 2 of AcctNo 001. The highest Amount (2100.00) is updated (to 0.00) first, then the next highest Amount (2000.00) is updated (to 1900.00) next.

4

Judging by the lack of replies, I guess this problem is so difficult or impractical to solve without using a cursor. Here's my solution which uses a cursor. Maybe this will give you an idea on how to translate this into a "cursorless" solution.

DECLARE @GroupControl varchar(25) = ''
DECLARE @RunningTotal money
DECLARE @ID Int, @AcctNo varchar(3), @GroupNo varchar(3), @Amount money, @GroupMaxAmt money

DECLARE Payments CURSOR STATIC FOR
SELECT ID, AcctNo, GroupNo, Amount, GroupMaxAmt FROM #tmp
ORDER BY AcctNo, GroupNo, Amount

OPEN Payments
FETCH NEXT FROM Payments INTO @ID, @AcctNo, @GroupNo, @Amount, @GroupMaxAmt

WHILE @@FETCH_STATUS = 0
BEGIN
IF @GroupControl <> @AcctNo + @GroupNo
BEGIN
SET @GroupControl = @AcctNo + @GroupNo
SET @RunningTotal = 0
END
SET @RunningTotal = @RunningTotal + @Amount
IF @RunningTotal > @GroupMaxAmt
BEGIN
UPDATE #tmp SET Amount = Amount - (@RunningTotal - @GroupMaxAmt)
WHERE ID = @ID
SET @RunningTotal = @GroupMaxAmt
END
FETCH NEXT FROM Payments INTO @ID, @AcctNo, @GroupNo, @Amount, @GroupMaxAmt
END

CLOSE Payments
DEALLOCATE Payments

SELECT * FROM #tmp

Sorry for the lack of indentation. The indentation is being removed. How do I post SQL code so the indentation is not removed?

Anyway, who is the SQL genius who will solve this problem without using a cursor? :wink: