I'm trying to run the following MS SQL query:
select PolicyCode, OccupancyCode, premiumcode, VersionNumber, rate
from COVERAGE_RATES as cov
group by PolicyCode, OccupancyCode, PremiumCode,VersionNumber, rate
having (VersionNumber = 25 or VersionNumber =
(
select MAX(versionnumber) from COVERAGE_RATES where
PolicyCode = 4 and OccupancyCode=2 and PremiumCode = cov.PremiumCode
) ) and PolicyCode = 4
AND OccupancyCode = 2
The idea is to take records where VersionNumber is 25 or, if there is no such version in the group, take the maximum number. That is if we have the table:
policyCode Version ...
----------------------
1 2
1 10
2 1
2 25
2 26
We should have the following result:
policyCode Version ...
----------------------
1 10
2 25
The Linq code is:
var res = (from c in CoverageRate
group c by c.PolicyCode
into rateGroup
where rateGroup.Any(r => r.VersionNumber == versionNumber ||
r.VersionNumber ==
CoverageRate.Where(c2 => c2.OccupancyCode == occupancyCode && c2.PolicyCode == policyCode)
.Max(c2 => c2.VersionNumber))
select rateGroup);
But I suppose I'm doing something wrong because when I try to take res.Count() I have the following error:
Unable to create a constant value of type '..CoverageRate'. Only primitive types ('such as Int32, String, and Guid') are supported in this context.
Could someone help me with this query?
Update - I think you are looking for this:
var res = from c in CoverageRate
group c by c.PolicyCode into rateGroup
select rateGroup.OrderByDescending(x => x.VersionNumber)
.FirstOrDefault();
from c in CoverageRate
group c by c.PolicyCode
into g
select new
{
policyCode = g.Key,
Version = (from t2 in g select t2.VersionNumber).Max()
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With