refering to the last screenshot following LINQ (taken from your post)
Group p By FName=p.Item(“FacilityName”).ToString Into grp=Group
Let ra= New Object() {FName, grp.Max(Function® CInt(r(“Beds”).toString.Trim)),grp.Sum(Function® Convert.ToDouble(r(“TotalCost”).toString.Replace("$","").Trim))}
Select BedCount.Rows.Add(ra)).CopyToDataTable()
and the reported output:
FName1,124,2906.32
FName2,120,1859.19
is reasonable and within the expectation (2 Groups, 2 rows, Max, Sums) as it deriveable
comparing to the expected output:
2,224,1
the LINQ / computing rows are unclear not confirmed deriveable
- 1 row, but having two groups
- 224, the deriveable computation could be: FN1 Max Bed + FN2 Max Beds = 124+120=224
- 1 the deriveable computation could be. count the groups where the Cost Sums > 2000
the 2,224,1 looks a little bit like it is computing the result of
FName1,124,2906.32
FName2,120,1859.19
Maybe you can do following:
dtGroupResult1 =
Group p By FName=p.Item(“FacilityName”).ToString Into grp=Group
Let ra= New Object() {FName, grp.Max(Function® CInt(r(“Beds”).toString.Trim)),grp.Sum(Function® Convert.ToDouble(r(“TotalCost”).toString.Replace(“$”,“”).Trim))}
Select BedCount.Rows.Add(ra)).CopyToDataTable()
Prepare an empty datatable (dtGroupResult2) with the column structure (NoOfFac, Beds, FacsOver2k)
Collect the different information from dtGroupResult1:
- NoOfFac = dtGroupResult1.Rows.Count
- Beds = dtGroupResult2.AsEnumerable.Sum(Function (x) Convert.ToDouble(x(ColNameOrIndex)))
- But result will be 244 and not 224
- FacsOver2k = dtGroupResult2.AsEnumerable.Where(Function (x) Convert.ToDouble(x(ColNameOrIndex)) > 2000).Count
So with this results dtGroupResult2 can be populated
Maybe this helps for your next steps.