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.

What would be the expected result here? Also, tag your question with the proper dbms, mysql <> sql server. — Felix Pamittan, May 18 '16 at 05:13
Kindly assist me in both SQL Server and MySQL. My First Preference is SQL Server. — , May 18 '16 at 05:14
Why you removed Snacks 1, but not e.g. Snacks 2 or Snacks 4? — Andrey Davydenko, May 18 '16 at 06:05
I need to roundup with 20 or below 20, you can remove any records till the total equal to 20 or lesser than 20, don't repeat if the total reached 20 or lesser than 20. — , May 18 '16 at 06:15

Vladimir Baranov · Accepted Answer · 2016-05-20T05:12:39.290

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 |
+----------+-----------+---------+

Good answer. Note - if you use order by payment desc you can get as close to 20 without going over. It was not a stated requirement but it might be desirable to some. — Joe C, May 18 '16 at 11:53
@JoeC, with ordering by `payment desc` there is a risk to delete all rows, if at least one payment is more than 20. In practice it is rather difficult to determine an "optimal" subset. I think it is called a [knapsack problem](https://en.wikipedia.org/wiki/Knapsack_problem) problem. SQL is not the best way to do it properly. — Vladimir Baranov, May 18 '16 at 12:08
@B.Balamanigandan, I don't know how to translate it to MySQL, because it doesn't support window functions. There must be many examples how to calculate running total in MySQL: http://stackoverflow.com/questions/664700/calculate-a-running-total-in-mysql , http://stackoverflow.com/questions/2563918/create-a-cumulative-sum-column-in-mysql — Vladimir Baranov, May 19 '16 at 23:10
@VladimirBaranov - If I want to Implement the same logic in MySQL means whats the solution. Kindly give the idea please. I'm not asking CTE in MySQL, I'm asking the logic in MySQL. — B.Balamanigandan, May 20 '16 at 04:30
@B.Balamanigandan, the idea of this query is to calculate a running/cumulative sum of `Payment` amounts partitioned by `PersonID`. Then delete all rows that have cumulative sum greater than the threshold (20). @IRPunch, you are welcome. — Vladimir Baranov, May 20 '16 at 05:13

score 0 · Answer 2 · answered May 19 '16 at 13:37

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

Delete some rows to bring each group total below the threshold

2 Answers2