2

I have two tables named Claim

EmployeeClaimId
      1144
      3481
      3452
      5760
      7296

and ClaimStatus

EmployeeClaimId   Amount   Version
       7            0.00      1
       7          300.00      3
       7          150.00      3
       7          400.00      1
       7          300.00      7
      14         1860.00      1
      14          541.00      7
      14          530.00      1
      14         2490.00      1

I want the sum of amount from second table where version is max for each claimid.

Can someone help?

0

3 Answers 3

5

What is the Claim table used for?

Well, you can try that:

select EmployeeClaimId, sum(Amount)
from ClaimStatus cs
join (
    select EmployeeClaimId, max(version) as version
    from ClaimStatus 
    group by EmployeeClaimId
    ) c on cs.EmployeeClaimId = c.EmployeeClaimId
        and cs.version = c.version
0
2

Since you don't specify version of sql-server I assume a recent one. For this type of query, window functions fits like hand in glove:

select EmployeeClaimId, sum(amount)
from (
    select EmployeeClaimId, Amount, Version
         , row_number() over (partition by EmployeeClaimId
                              order by Version desc) as rn
    from ClaimStatus
) as T
where rn = 1
group by EmployeeClaimId;

From the inner select we grab the last version of each EmployeeClaimId (order by Version desc & rn = 1). Then we sum all amounts per EmployeeClaimId

-2

Here you go:

SELECT SUM( Amount )
FROM ClaimStatus
WHERE Version IN
(
  SELECT MAX( Version )
  FROM ClaimStatus
  GROUP BY EmployeeClaimId
)
GROUP BY EmployeeClaimId;
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.