Detail table sum in linq query

Question

0.00/5 (No votes)

See more:

Hi friends,
I have three table these are follows

VisitDetails

VisitId    visitCode   visitDate 
1          V001      15-12-2015

PatientBill

PatientBillId    VisitID     BillCode     PatientPayable      CompanyPayable
1                1           B001          100                 50
2                1           B002          80                  40

PatientBillDetails

PBillDetailsId      PatientBIllId      Gross    Discount    NetPayable
1                   1                  100      25          75
2                   1                  100      25          75
3                   2                  80       20          60
4                   2                  80       20          60

I want record like that
First Output

VisitCode      PatientPayable        CompanyPayable      NetPayable
V001           180                   90                  270

Second Output

VisitCode     BillCode     PatientPayable        CompanyPayable      NetPayable
V001          B001         100                   50                  150
V001          B002         80                    40                  120

Note:- There are some criteria so, I can't add PatientPayable and CompanyPayable amount for NetPayable. Everytime NetPayable amount should come from PatientBillDetails.

What I have tried:

C#

var result= from VD in VisitDetails 
            join PB in PatientBill on VD.VisitId equal PB.VisitId 
            join PBD in PatientBillDetails on PB.PatientBillId equal PBD.PatientBillId
group new
{
PB.PatientPayable,
PB.CompanyPayable,
PBD.NetPayable
}
by new
{
VD.VisitCode
}
into data
select new
{
VisitCode= data.Key.VisitCode,
PatientPayable= data.sum(x=> x.PatientPayable),
CompanyPayable= data.sum(x=> x.CompanyPayable),
NeyPayable= data.sum(x=> x.NetPayable)
}

Now my output is

VisitCode     PatientPayable     ComapanyPayable      NetPayable
V001          360                180                  270

But I want Output like this

VisitCode     PatientPayable     ComapanyPayable      NetPayable
V001          180                90                  270

Posted 14-Dec-16 19:43pm

Shambhoo kumar

Updated 17-Dec-16 11:25am

Maciej Los

v2

Add a Solution

Comments

Jon McKee 15-Dec-16 2:40am

Isn't this intended behavior? You have multiple entries for the bill details, so joining them into the patient bill will produce a result {PB1, PBD1}, {PB1, PBD2}, {PB2, PBD3}, {PB2, PBD4} for each of the detail:bill pairs. If not I'm not sure what the purpose of multiple PatientBillDetails entries is. If you could clarify that I may be able to help :)

Shambhoo kumar 15-Dec-16 2:48am

Let me explain the scenario, for one patient we can create multiple visit and for each visit we can create multiple patient bill and for each patient bill we can create multiple bill details.

Jon McKee 15-Dec-16 3:09am

Ah. Unless I know what the purpose of multiple bill details is I can't really help. Do you choose the lowest of the possible bill details? Highest? An average? Unless you choose one or use an algorithm to combine them into a single value you'll always get multiple groupings.

Shambhoo kumar 15-Dec-16 2:51am

Below Linq query working fine but it's very slow, please help me to improve this query

var result= from VD in VisitDetails
join PB in PatientBill on VD.VisitId equal PB.VisitId
group new
{
PB
}
by new
{
VD.VisitCode
}
into data
select new
{
VisitCode= data.Key.VisitCode,
PatientPayable= data.sum(x=> x.PB.PatientPayable),
CompanyPayable= data.sum(x=> x.PB.CompanyPayable),
NeyPayable= data.Sum(item=> item.PB.PatientBillDetails.Sum(x=> x.NetPayable))
}

Jon McKee 15-Dec-16 3:08am

You don't need to create an anonymous type for one.
group PB by VD.VisitCode into data
would avoid the allocation of a new type PB times for each grouping of VD.VisitCode.

Shambhoo kumar 15-Dec-16 3:22am

can you give you suggested query.

Jon McKee 15-Dec-16 3:46am

Honestly I can't. Multiple PBD produce multiple groupings with the join of VD and PB (or the join of PBD and PB with VD). Unless there's a way to filter the PBD based on some criteria you'll always have this problem. The example code you gave above that ignores PBD has optimizations (preventing the allocation of unnecessary objects), but I'm not sure it accomplishes what you want. A simple solution would be to divide the result by 2 but if the PBD aren't equal this solution doesn't work. I just don't have enough information on the purpose of this task to give you an adequate answer beyond what I already have :(

Shambhoo kumar 15-Dec-16 4:01am

It's ok McKee, thanks for your time. I really appreciate your effort and time. :)

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 · 2016-12-17T11:25:00

For such of requirement you have to "group" data in a different way. Please, check out below example:

C#

DataTable VisitDetails = new DataTable();
VisitDetails.Columns.Add(new DataColumn("VisitId", typeof(int)));
VisitDetails.Columns.Add(new DataColumn("visitCode", typeof(string)));
VisitDetails.Columns.Add(new DataColumn("visitDate", typeof(DateTime)));
VisitDetails.Rows.Add(new object[]{1, "V001", new DateTime(2015,12,15)});

DataTable PatientBill = new DataTable();
PatientBill.Columns.Add(new DataColumn("PatientBillId", typeof(int)));
PatientBill.Columns.Add(new DataColumn("VisitId", typeof(int)));
PatientBill.Columns.Add(new DataColumn("BillCode", typeof(string)));
PatientBill.Columns.Add(new DataColumn("PatientPayable", typeof(int)));
PatientBill.Columns.Add(new DataColumn("CompanyPayable", typeof(int)));
PatientBill.Rows.Add(new object[]{1, 1, "B001", 100, 50});
PatientBill.Rows.Add(new object[]{2, 1, "B002", 80, 40});

DataTable PatientBillDetails = new DataTable();
PatientBillDetails.Columns.Add(new DataColumn("PBillDetailsId", typeof(int)));
PatientBillDetails.Columns.Add(new DataColumn("PatientBIllId", typeof(int)));
PatientBillDetails.Columns.Add(new DataColumn("Gross", typeof(int)));
PatientBillDetails.Columns.Add(new DataColumn("Discount", typeof(int)));
PatientBillDetails.Columns.Add(new DataColumn("NetPayable", typeof(int)));
PatientBillDetails.Rows.Add(new object[]{1, 1, 100, 25, 75});
PatientBillDetails.Rows.Add(new object[]{2, 1, 100, 25, 75});
PatientBillDetails.Rows.Add(new object[]{3, 2, 80, 20, 60});
PatientBillDetails.Rows.Add(new object[]{4, 2, 80, 20, 60});


var result2 = (from PB in PatientBill.AsEnumerable()
			select new
			{
				VisitId = PB.Field<int>("VisitId"),
				VisitCode = (from VD in VisitDetails.AsEnumerable()
								where VD.Field<int>("VisitId")==PB.Field<int>("VisitId")
								select VD.Field<string>("visitCode")).First(),
				BillCode = PB.Field<string>("BillCode"),
				PatientPayable = PB.Field<int>("PatientPayable"),
				CompanyPayable = PB.Field<int>("CompanyPayable"),
				NetPayable = (from PBD in PatientBillDetails.AsEnumerable()
								where PBD.Field<int>("PatientBIllId")==PB.Field<int>("PatientBIllId") 
								select PBD.Field<int>("NetPayable")).Sum(),
			}).ToList();
//see result #2
var result1 = (from r in result2
				group r by r.VisitCode into g
				select new
				{
					VisitCode = g.Key,
					PatientPayable = g.Sum(x=>x.PatientPayable),
					CompanyPayable = g.Sum(x=>x.CompanyPayable),
					NetPayable = g.Sum(x=>x.NetPayable)
				}).ToList();
//see result #1

Result #1

VisitCode PatientPayable CompanyPayable NetPayable
V001      180            90             270

Resutl #2

VisitId VisitCode BillCode PatientPayable CompanyPayable NetPayable
1       V001      B001     100            50             150 
1       V001      B002     80             40             120