Delete some rows to bring each group total below the threshold

Question

I have a Ledger table:

CREATE TABLE Ledger
 (
     PersonID int,
     Narration varchar(255),
     Payment int(255)
 ); 

INSERT INTO Ledger(PersonID, Narration, Payment)
 VALUES (1, 'Snacks 1', 5); 
INSERT INTO Ledger(PersonID, Narration, Payment)
 VALUES (1, 'Snacks 2', 10); 
INSERT INTO Ledger(PersonID, Narration, Payment)
 VALUES (2, 'Snacks 3', 7); 
INSERT INTO Ledger(PersonID, Narration, Payment)
 VALUES (1, 'Snacks 4', 6); 
INSERT INTO Ledger(PersonID, Narration, Payment)
 VALUES (2, 'Snacks 5', 3); 
INSERT INTO Ledger(PersonID, Narration, Payment)
 VALUES (1, 'Snacks 6', 1); 

The table looks like this:

PersonID          Narration          Payment
_____________________________________________
    1             Snacks 1                5
    1             Snacks 2               10
    2             Snacks 3                7
    1             Snacks 4                6
    2             Snacks 5                3
    1             Snacks 6                1

Here PersonID=1 totally spent 22 and PersonID=2 totally spent 10.

My requirement is to reduce the total Payment to be below or equal to 20. There is no unique columns. I wish to delete records to make the total Payment below or equal to 20.

In the above table, PersonID=1 has a total Payment greater than 20, so I need to delete some records to reduce the total payment.

My Expected Output

PersonID          Narration          Payment
_____________________________________________

    1             Snacks 2               10
    2             Snacks 3                7
    1             Snacks 4                6
    2             Snacks 5                3
    1             Snacks 6                1

Here I removed

1             Snacks 1                5

Now the total Payment of PersonID=1 is 17, which is below 20.

Based on logic we have to delete the records.

Kindly assist me in both SQL Server and MySQL. My First Preference is SQL Server.

Answer 1

Here is one possible variant for SQL Server 2012+.

Sample data

CREATE TABLE Ledger
(
    PersonID int,
    Narration varchar(255),
    Payment int
);

INSERT INTO Ledger(PersonID, Narration, Payment) VALUES 
(1, 'Snacks 1', 5),
(1, 'Snacks 2', 10),
(2, 'Snacks 3', 7),
(1, 'Snacks 4', 6),
(2, 'Snacks 5', 3),
(1, 'Snacks 6', 1);

SELECT *
FROM Ledger
ORDER BY PersonID, Payment;

+----------+-----------+---------+
| PersonID | Narration | Payment |
+----------+-----------+---------+
|        1 | Snacks 6  |       1 |
|        1 | Snacks 1  |       5 |
|        1 | Snacks 4  |       6 |
|        1 | Snacks 2  |      10 |
|        2 | Snacks 5  |       3 |
|        2 | Snacks 3  |       7 |
+----------+-----------+---------+

Query

We can calculate the running total to determine which rows we want to keep and which to delete. You can tune the logic of row selection by choosing the sorting in the running total. In this example I'll calculate the running total starting from the smallest Payment, so rows with smallest Payment will remain.

This query shows the calculations, to understand how it works:

WITH
CTE
AS
(
    SELECT
        PersonID
        ,Narration
        ,Payment
        ,SUM(Payment) OVER 
            (PARTITION BY PersonID ORDER BY Payment
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ss
    FROM Ledger
)
SELECT *
FROM CTE
ORDER BY PersonID, Payment;


+----------+-----------+---------+----+
| PersonID | Narration | Payment | ss |
+----------+-----------+---------+----+
|        1 | Snacks 6  |       1 |  1 |
|        1 | Snacks 1  |       5 |  6 |
|        1 | Snacks 4  |       6 | 12 |
|        1 | Snacks 2  |      10 | 22 |
|        2 | Snacks 5  |       3 |  3 |
|        2 | Snacks 3  |       7 | 10 |
+----------+-----------+---------+----+

This query actually deletes rows:

WITH
CTE
AS
(
    SELECT
        PersonID
        ,Narration
        ,Payment
        ,SUM(Payment) OVER 
            (PARTITION BY PersonID ORDER BY Payment
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ss
    FROM Ledger
)
DELETE FROM CTE
WHERE ss > 20;

Result

SELECT *
FROM Ledger
ORDER BY PersonID, Payment;

+----------+-----------+---------+
| PersonID | Narration | Payment |
+----------+-----------+---------+
|        1 | Snacks 6  |       1 |
|        1 | Snacks 1  |       5 |
|        1 | Snacks 4  |       6 |
|        2 | Snacks 5  |       3 |
|        2 | Snacks 3  |       7 |
+----------+-----------+---------+

Answer 2

You can also the below query to get the required output :-

DECLARE @PID int ,@PID1 int,@Narr VARCHAR(250),@Payment decimal(18,2),@Payment1 decimal(18,2),@cnt int

SET @cnt = 20
set @Payment1=0
set @PID1=0

Create table #t1(PersonID int,Narration varchar(250),Payment decimal(18,2));

DECLARE db_cursor CURSOR FOR  
SELECT PersonID,Narration,Payment from Ledger order by personid ,Payment

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @PID,@Narr,@Payment   

WHILE @@FETCH_STATUS = 0   
BEGIN  
if (@PID1 <> @PID)
BEGIN
SET @Payment1 = 0
END
set @PID1 = @PID
SET @Payment1 = @Payment1 + @Payment

If(@Payment1 <= 20)
begin

Insert into #t1(PersonID,Narration,Payment)
values(@PID,@Narr,@Payment)

end
FETCH NEXT FROM db_cursor INTO @PID,@Narr,@Payment    
end
CLOSE db_cursor   
DEALLOCATE db_cursor

select * from #t1
order by personid
drop table #t1

Tested the same and got below output for below input :-

Input :-

1   Snacks 1    5
1   Snacks 2    10
1   Snacks 4    6
1   Snacks 6    1
2   Snacks 5    3
2   Snacks 3    7
3   Snacks 7    15
3   Snacks 8    15
4   Snacks 9    10

Output :-

 1  Snacks 6    1.00
 1  Snacks 1    5.00
 1  Snacks 4    6.00
 2  Snacks 5    3.00
 2  Snacks 3    7.00
 3  Snacks 7    15.00
 4  Snacks 9    10.00