Блог

У каждой претензии может быть несколько заявителей. Претензия рассматривается 'Open' , если хотя бы один из этих заявителей имеет ClaimantStatus = 0

Итак, мне нужно получить данные на уровне претензии (не заявителей) и создать столбец, ClaimStatus который указывал бы, является ли претензия Open или Closed .

Для каждого ClaimID мне нужно проверить, есть ли хотя бы у одного заявителя ClaimantStatus = 0 (открыто), и если это верно, то столбец ClaimStatus должен быть = 'Open' , в противном случае должен быть = '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
  

Правильный ответ должен быть таким:

Похоже, простого GROUP BY достаточно. Claim Открыт, если хотя бы у одного Claimant есть ClaimantStatus = 0, мы можем использовать MIN для определения этого. Здесь мы полагаемся на ClaimantStatus наличие только значений 0 и 1 .

 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;
  

Я думаю, что использование Exists — это то, что вы ищете

 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
  

В зависимости от вашей ситуации вы также можете рассмотреть возможность сгруппированного запроса. Это может быть более эффективным, поскольку не нужно выполнять два отдельных поиска для подсчета и статуса. Для этого требуется, чтобы по каждой претензии был по крайней мере 1 заявитель.

 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