LINQ sum having if condition

Question

0.00/5 (No votes)

See more:

Here is the sample data for my problem,

List<EMPINSURED> lstEmpIns = new List<EMPINSURED>{
    new EMPINSURED{ EmpID = 558, TOTAL_INSURED_AMT = 50000},
    new EMPINSURED{ EmpID = 559, TOTAL_INSURED_AMT = 75000} 
};


List<EMP_ROUTINE_CHECKUP_HISTORY> lstEmpRoutineChkHist = new List<EMP_ROUTINE_CHECKUP_HISTORY>{
    new EMP_ROUTINE_CHECKUP_HISTORY{ ID = 1, EmpID = 558, ROUTINE_CHECKUP = 1, CHECK_UP_CHARGE = 20000},
    new EMP_ROUTINE_CHECKUP_HISTORY{ ID = 2, EmpID = 559, ROUTINE_CHECKUP = 1, CHECK_UP_CHARGE = 35000},
	new EMP_ROUTINE_CHECKUP_HISTORY{ ID = 3, EmpID = 558, ROUTINE_CHECKUP = 2, CHECK_UP_CHARGE = 10000},
	new EMP_ROUTINE_CHECKUP_HISTORY{ ID = 4, EmpID = 559, ROUTINE_CHECKUP = 2, CHECK_UP_CHARGE = 10000},
	new EMP_ROUTINE_CHECKUP_HISTORY{ ID = 5, EmpID = 558, ROUTINE_CHECKUP = 3, CHECK_UP_CHARGE = 20000}
};

In a year, an Employee is allowed only 3(three) routine checkups. When an employee goes for routine checkup, his/her check up charges must be deducted from the total insured amount after every checkups. For the next routine check up, employee will be awarded check up charges only for the remainig amount he/she has from the total insured amount, any expenses more than that the company will not bear.
So for the logic for calculating REMAINING AMOUNT would be somewhat like this ::

int var = 0;
if(ROUTINE_CHECKUP == 1)
{
	REMAINING_AMOUNT = TOTAL_INSURED_AMT - CHECK_UP_CHARGE;
	var = CHECK_UP_CHARGE;
} 
else if (ROUTINE_CHECKUP == 2)
{
	REMAINING_AMOUNT = TOTAL_INSURED_AMT - (CHECK_UP_CHARGE + var);
	var = CHECK_UP_CHARGE + var;
}
else if (ROUTINE_CHECKUP == 3)
{
	REMAINING_AMOUNT = TOTAL_INSURED_AMT - (CHECK_UP_CHARGE + var);
	var = CHECK_UP_CHARGE + var;
}

How to develop the LINQ query to get the below report data using this logic or there may some better way to do this.
Please see the link of the image for report format.
query — ImgBB[^]

What I have tried:

I have no idea how to do this. Please help me with this problem.

Posted 1-Sep-20 9:00am

nischalinn

Updated 1-Sep-20 22:15pm

Add a Solution

2 solutions

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)

OriginalGriff · Answer 1 · 2020-09-01T09:40:00

We are more than willing to help those that are stuck: but that doesn't mean that we are here to do it all for you! We can't do all the work, you are either getting paid for this, or it's part of your grades and it wouldn't be at all fair for us to do it all for you.

So we need you to do the work, and we will help you when you get stuck. That doesn't mean we will give you a step by step solution you can hand in!
Start by explaining where you are at the moment, and what the next step in the process is. Then tell us what you have tried to get that next step working, and what happened when you did.

Start with your course notes for the last few lectures and read them carefully. This isn't a difficult bit if homework if you think about it for a few minutes.

Maciej Los · Answer 2 · 2020-09-01T22:15:00

I'd try something like this:

C#

var lstps =  (from e in lstEmpIns
    join h in lstEmpRoutineChkHist on e.EmpID equals h.EmpID
    select new
    {
        EmpID = e.EmpID,
        INITIAL_AMT = e.TOTAL_INSURED_AMT,
        ROUTINE_CHECKUP = h.ROUTINE_CHECKUP,
        CHECK_UP_CHARGE = h.CHECK_UP_CHARGE,
        REMAINING_SUM = e.TOTAL_INSURED_AMT - lstEmpRoutineChkHist.Where(x=> x.EmpID == e.EmpID && x.ROUTINE_CHECKUP <= h.ROUTINE_CHECKUP).Sum(x=>x.CHECK_UP_CHARGE)
        //or
        //REMAINING_SUM = e.TOTAL_INSURED_AMT - lstEmpRoutineChkHist.Where(x=> x.EmpID == e.EmpID).TakeWhile(x=>x.ROUTINE_CHECKUP <= h.ROUTINE_CHECKUP).Sum(x=>x.CHECK_UP_CHARGE)
    })
    .ToList();

foreach(var ps in lstps)
{
    Console.WriteLine($"{ps.EmpID}\t{ps.INITIAL_AMT}\t{ps.ROUTINE_CHECKUP}\t{ps.CHECK_UP_CHARGE}\t{ps.REMAINING_SUM}");
}

version #2 - by using let clause[^]

C#

var lstps =  (from e in lstEmpIns
    join h in lstEmpRoutineChkHist on e.EmpID equals h.EmpID
    let cuc_sum = lstEmpRoutineChkHist.Where(x=> x.EmpID == e.EmpID && x.ROUTINE_CHECKUP <= h.ROUTINE_CHECKUP).Sum(x=>x.CHECK_UP_CHARGE)
    select new
    {
        EmpID = e.EmpID,
        INITIAL_AMT = e.TOTAL_INSURED_AMT,
        ROUTINE_CHECKUP = h.ROUTINE_CHECKUP,
        CHECK_UP_CHARGE = h.CHECK_UP_CHARGE,
        REMAINING_SUM = e.TOTAL_INSURED_AMT - cuc_sum
    })
    .ToList();

Result:

558  50000  1  20000  30000
558  50000  2  10000  20000
558  50000  3  20000  0
559  75000  1  35000  40000
559  75000  2  10000  30000

Note: there's, probably, better way to achieve that...

Good luck!

LINQ sum having if condition

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0