我需要为每个PolicyNumber创建一行。但是ClaimStatus是问题所在。
如果我有超过1个ClaimCount,则需要检查ClaimStatus ='Open'之一。
如果其中之一是“开放的”,那么ClaimStatus
就需要Open
整体的价值PolicyNumber
,否则ClaimStatus = 'Closed'
当前结果如下:
PolicyNumber ClaimCount ClaimStatus
---------------------------------------
Pol1 2 Closed
Pol1 2 Open
Pol2 2 Closed
Pol2 2 Closed
Pol3 1 Closed
Pol4 3 Open
Pol4 3 Closed
样例代码:
declare @ClaimsTable table (PolicyNumber varchar(50), ClaimCount int, ClaimStatus varchar(50))
insert into @ClaimsTable
values ('Pol1', 2, 'Closed'), ('Pol1', 2, 'Open'),
('Pol2', 2, 'Closed'), ('Pol2', 2, 'Closed'),
('Pol3', 1, 'Closed'),
('Pol4', 3, 'Open'), ('Pol4', 3, 'Closed')
select * from @ClaimsTable
结果应该是这样的:
如果只有两个值,则可以使用max()
:
SELECT policynumber, count(*) AS numclaims,
MAX(claimstatus)
FROM @ClaimsTable
GROUP BY policynumber;
这取决于'Open'
>的事实'Closed'
。一个更通用的解决方案可能是:
SELECT policynumber, count(*) AS numclaims,
(CASE WHEN SUM(CASE WHEN claimstatus = 'Open' THEN 1 ELSE END) > 0
THEN 'Open'
ELSE MIN(claimstatus)
END) AS claimstatus
FROM @ClaimsTable
GROUP BY policynumber;
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句