CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
Serdia
Serdia

Reputation: 4428

how to check whether one of Claimants have Status 0 and assign Claim Status based on that

Each Claim can have multiple Claimants. Claim is considered 'Open' if at least one of those claimants have ClaimantStatus = 0

So I need to retrieve data on Claim Level (not Claimants) and Create column ClaimStatus that would indicate whether Claim is Open or Closed.

For each ClaimID, I need to check whether there is at least one Claimant have ClaimantStatus = 0 (Open) and if this is true then column ClaimStatus should be = 'Open', otherwise should be = 'Closed'

 declare @ClaimsTable table (ClaimID varchar(20))
 insert into @ClaimsTable values ('Claim1'),
                                 ('Claim2'),
                                 ('Claim3'),
                                 ('Claim4')

declare @ClaimantsTable table (ClaimID varchar(20),  ClaimantName varchar(50), ClaimantStatus int)
insert into @ClaimantsTable values ('Claim1','Claimant1',1),
                                   ('Claim1','Claimant2',0),
                                   ('Claim1','Claimant3',1),
                                   ('Claim2','Claimant2',0),
                                   ('Claim3','Claimant1',1),
                                   ('Claim3','Claimant2',1),
                                   ('Claim3','Claimant3',1),
                                   ('Claim4','Claimant1',0),
                                   ('Claim4','Claimant2',0)
--select * from @ClaimantsTable

select ClaimID,
        (select count (ClaimantName) from @ClaimantsTable ct where ct.ClaimID = c.ClaimID) as NumberOfClaimants,
    --below statement does not work correctly
        (select top 1 case when ClaimantStatus in (0) then 'Open' Else 'Closed' end as t from @ClaimantsTable ct where ct.ClaimID = c.ClaimID) as ClaimStatus
from @ClaimsTable c

Right answer should be like this:

enter image description here

Upvotes: 1

Views: 213

Answers (2)

Vladimir Baranov
Vladimir Baranov

Reputation: 32693

It looks like a simple GROUP BY is enough. The Claim is open if at least one Claimant has ClaimantStatus = 0, we can use MIN to determine that. Here we rely on ClaimantStatus having only 0 and 1 values.

SELECT
    Claims.ClaimID
    ,COUNT(*) AS NumberOfClaimants
    ,CASE WHEN MIN(Claimants.ClaimantStatus) = 0 
        THEN 'Open' 
        ELSE 'Closed' 
    END AS ClaimStatus
FROM
    @ClaimsTable AS Claims
    INNER JOIN @ClaimantsTable AS Claimants ON Claimants.ClaimID = Claims.ClaimID
GROUP BY
    Claims.ClaimID
ORDER BY
    Claims.ClaimID;

Upvotes: 2

AlwaysLoadingData
AlwaysLoadingData

Reputation: 221

I think using Exists is what you're looking for

select ClaimID,
    (select count (ClaimantName) from @ClaimantsTable ct where ct.ClaimID = c.ClaimID) as NumberOfClaimants,
    CASE WHEN EXISTS(SELECT 1 
                    FROM @ClaimantsTable AS claimant 
                    WHERE claimant.ClaimID = c.ClaimID
                        AND claimant.ClaimantStatus = 0) 
        THEN 'Open' 
        ELSE 'Closed' END AS claimStatus
from @ClaimsTable c

Depending on your situation, you might also consider a grouped query. This can be more efficient by not needing to do two separate lookups for the count and status. This does require that there is at least 1 claimant for every claim.

SELECT
    claim.ClaimId
    ,COUNT(*) AS NumberOfClaimants
    ,CASE WHEN SUM(CASE WHEN claimant.ClaimantStatus = 0 THEN 1 ELSE 0 END) > 0 THEN 'Open' ELSE 'Closed' END AS claimStatus
FROM
    @ClaimsTable AS claim
    INNER JOIN @ClaimantsTable AS claimant
        ON claim.ClaimID = claimant.ClaimID
GROUP BY
    claim.ClaimID

Upvotes: 1

Related Questions