Remove duplicate until sum value equal to my text box value in C#, windows forms, ASP.NET

Question

1.00/5 (1 vote)

See more:

Hi to all,

i am trying to remove duplicate until sum value equal to textbox value

My Table Name A_table

ID	S_Number	Amount
1	55	100
2	55	100
3	66	100
4	66	100
5	77	100
6	88	100
7	88	100
8	99	100
9	44	100
10	88	100

using this quary i can remove the all duplicates values but i need
textbox equal to table Amount sum value..

Ex: if text box value is 900 then in data table should be delete 100 amout only sum of 1000 amount for duplicates delete ...

What I have tried:

I wrote Linq Query

C#

DataClasses1DataContext DB = new DataClasses1DataContext();

var f1 = (from k in DB.A_table
          select k.Amount).Sum();


***// Here i have to write something for "f1" sum value equal to textbox value
***// run "f2" quary untill

var f2 = DB.A_table
       .GroupBy(g => g.S_Number)
       .Where(s => s.Count() > 1)
       .Select(R => R.First());

 foreach (var l in f2)
         {
           DB.A_table.DeleteOnSubmit(l);
        }

            DB.SubmitChanges();

Posted 23-Jun-18 17:34pm

Member 11917879

Updated 14-Jan-19 3:50am

Maciej Los

v2

Add a Solution

Comments

Richard Deeming 26-Jun-18 13:45pm

If the sum has to be less than or equal to the value, then just loop through the rows calculating the cumulative sum. Once that sum exceeds the required value, delete the current row and any subsequent rows.

If the sum has to be exactly equal to the value, then that's a much more difficult problem. There may not even be a solution for a particular data set. Eg: taking your example data from the question, what happens if the textbox value is 942? No combination of deletions will make the sum of the rows equal to that value.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2019-01-14T03:50:00

I'm not sure i understand you well, but seems you want to delete rows from table untill the sum of duplicated rows is less or equal to 900. If yes, you can use Linq TakeWhile method[^].

See:

C#

DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[]
	{
		new DataColumn("ID", typeof(int)),
		new DataColumn("S_Number", typeof(int)),
		new DataColumn("Amount", typeof(int)),
	});

dt.Rows.Add(new object[]{1, 55, 100});
dt.Rows.Add(new object[]{2, 55, 100});
dt.Rows.Add(new object[]{3, 66, 100});
dt.Rows.Add(new object[]{4, 66, 100});
dt.Rows.Add(new object[]{5, 77, 100});
dt.Rows.Add(new object[]{6, 88, 100});
dt.Rows.Add(new object[]{7, 88, 100});
dt.Rows.Add(new object[]{8, 99, 100});
dt.Rows.Add(new object[]{9, 44, 100});
dt.Rows.Add(new object[]{10, 88, 100});

int condition = 900;
var rows2delete = dt.AsEnumerable()
	.GroupBy(x=>x.Field<int>("S_Number")) //group data by S_Number
	.Where(grp=>grp.Count()>1)  //get only duplicated data
	.TakeWhile(grp=>grp.Sum(x=>x.Field<int>("Amount"))<=condition); //take until condition is met
	
Console.WriteLine("Duplicated data to delete:");
foreach(var r in rows2delete)
{
	Console.WriteLine("S_Number = {0} | Amount = {1}", r.Select(x=>x.Field<int>("S_Number")).First(), r.Sum(x=>x.Field<int>("Amount")));
}

Result:

Duplicated data to delete:
S_Number = 55 | Amount = 200
S_Number = 66 | Amount = 200
S_Number = 88 | Amount = 300