Btukfyl

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

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)

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;

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 |

+-------------+-------------+