How to eliminate Claims with ClaimStatus = 1 based

问题:

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:

Windowed functions can only appear in the SELECT or ORDER BY clauses

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

回答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)

回答2:

As the error says, you can't put LAST_VALUE in the WHERE, but you can put it in the SELECT of a CTE, and then reference that in the WHERE:

WITH CTE AS(
    SELECT ClaimNumber,
           ActivityID,
           Activity,
           ActivityDate,
           ClaimStatus,
           LAST_VALUE(ClaimStatus) OVER (PARTITION BY ClaimNumber ORDER BY ActivityDate
                                         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LatestStatus
    FROM @TempTable)
SELECT ClaimNumber,
       ActivityID,
       Activity,
       ActivityDate,
       ClaimStatus
FROM CTE
WHERE LatestStatus != 1;

回答3:

If I'm reading your question right, it seems like what you're interested in is finding out if the last status of a claim is that it's closed. If that's correct, we just need to find the last record and then check it's status. This seems to work:

SELECT
  tt.ClaimNumber,
  tt.ClaimStatus
FROM
  @TempTable AS tt
JOIN
  (
    SELECT
      t.ClaimNumber
     ,MAX(t.ActivityDate) AS LastActivity
    FROM
      @TempTable AS t
    GROUP BY
      t.ClaimNumber
  )          AS s
    ON
    s.ClaimNumber = tt.ClaimNumber
      AND s.LastActivity = tt.ActivityDate
WHERE
  tt.ClaimStatus <> 1;

Results:

+-------------+-------------+
| ClaimNumber | ClaimStatus |
+-------------+-------------+
| Claim2      |           0 |
+-------------+-------------+