如何根据活动日期消除 ClaimStatus = 1 的索赔.SQL Server 2012

问题描述

我的数据集中有 3 个不同的声明.

ClaimStatus 只能是 1 或 0.表示打开 (0) 或关闭 (1).

如何消除那些具有最后一个 ClaimStatus = 1 的声明.

我尝试在我的 WHERE 子句中使用 Last_Value 函数，但出现错误:

窗口函数只能出现在 SELECT 或 ORDER BY 子句中

查询结果应该只返回Claim2，因为ClaimStatus仍然是打开的:

代码:

声明@TempTable表(ClaimNumber varchar(50),活动 ID 整数，活动 varchar(50),ActivityDate 日期时间，声明状态 int)插入@TempTable值 ('Claim1', 0, 'ClaimCreated', '2018-05-04 10:58:56.510', 0),('Claim1', 4, 'ReserveCreated', '2018-05-09 09:52:52.327', 0),('Claim1', 6, 'PaymentCreated', '2018-05-15 13:17:47.920', 0),('Claim1', 8, 'ClaimClosed', '2018-11-01 10:53:00.087', 1),('Claim2', 0, 'ClaimCreated', '2018-05-04 10:58:56.510', 0),('Claim2', 4, 'ReserveCreated', '2018-05-09 09:52:52.327', 0),('Claim2', 6, 'PaymentCreated', '2018-05-15 13:17:47.920', 0),('Claim3', 0, 'ClaimCreated', '2018-05-04 10:58:56.510', 0),('Claim3', 4, 'ReserveCreated', '2018-05-09 09:52:52.327', 0),('Claim3', 6, 'PaymentCreated', '2018-05-15 13:17:47.920', 0),('Claim3', 8, 'ClaimClosed', '2018-11-01 10:53:00.087', 1)选择 *来自@TempTable其中 LAST_VALUE(ClaimStatus) over (partition by ClaimNumber order by ActivityDate desc) <>1

解决方案

另外如果只有2个状态你也可以做

WITH cte as (选择索赔号从@TempTable按索赔编号分组拥有 MAX(ClaimStatus) = 0)选择 t.*从@TempTable tWHERE ClaimNumber IN (SELECT * FROM cte)

I have 3 different claims in my dataset.

ClaimStatus can be only 1 or 0. Which indicates Open (0) or Closed (1).

How to eliminate those claims that have the last ClaimStatus = 1.

I tried to use Last_Value function in my WHERE clause but got an error:

Query result should return only Claim2, because ClaimStatus is still open:

Code:

declare @TempTable table 
                   (
                        ClaimNumber varchar(50), 
                        ActivityID int, 
                        Activity varchar(50), 
                        ActivityDate datetime, 
                        ClaimStatus int
                   )

insert into @TempTable 
values ('Claim1', 0, 'ClaimCreated', '2018-05-04 10:58:56.510', 0),
       ('Claim1', 4, 'ReserveCreated', '2018-05-09 09:52:52.327', 0),
       ('Claim1', 6, 'PaymentCreated', '2018-05-15 13:17:47.920', 0),
       ('Claim1', 8, 'ClaimClosed', '2018-11-01 10:53:00.087', 1),
       ('Claim2', 0, 'ClaimCreated', '2018-05-04 10:58:56.510', 0),
       ('Claim2', 4, 'ReserveCreated', '2018-05-09 09:52:52.327', 0),
       ('Claim2', 6, 'PaymentCreated', '2018-05-15 13:17:47.920', 0),
       ('Claim3', 0, 'ClaimCreated', '2018-05-04 10:58:56.510', 0),
       ('Claim3', 4, 'ReserveCreated', '2018-05-09 09:52:52.327', 0),
       ('Claim3', 6, 'PaymentCreated', '2018-05-15 13:17:47.920', 0),
       ('Claim3', 8, 'ClaimClosed', '2018-11-01 10:53:00.087', 1)

select *
from @TempTable
where LAST_VALUE(ClaimStatus) over (partition by ClaimNumber order by ActivityDate desc) <> 1

解决方案

SQL DEMO

WITH cte as (
    SELECT ClaimNumber, 
           ClaimStatus,
           ROW_NUMBER() over (PARTITION BY ClaimNumber ORDER BY ActivityDate DESC) as rn
    FROM @TempTable
)
SELECT t.*
FROM @TempTable t
JOIN (SELECT *
      FROM cte
      WHERE rn = 1) f
  ON t.ClaimNumber = f.ClaimNumber
 AND f.ClaimStatus = 0

OUTPUT

Additionally if only 2 status you also can do

WITH cte as (
    SELECT ClaimNumber
    FROM @TempTable
    GROUP BY ClaimNumber
    HAVING MAX(ClaimStatus) = 0
)
SELECT t.*
FROM @TempTable t
WHERE ClaimNumber IN (SELECT * FROM cte)

这篇关于如何根据活动日期消除 ClaimStatus = 1 的索赔.SQL Server 2012的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！