How to replace all previous values based on last value SQL Server 2017

Question

Sample data:

declare @T1 table 
            (
                ClaimID int, 
                Person varchar(20), 
                TransactionDate date,
                Status varchar(20)
            )

insert into @T1 
values (1, 'Person1', '2018-03-05','Open')
  ,(1, 'Person2', '2018-03-10','Open')
  ,(1, 'Person2', '2018-03-15','Closed')
  ,(2, 'Person3', '2018-03-15','Open')
  ,(2, 'Person3', '2018-03-23','Closed')
  ,(3, 'Person4', '2018-04-18','Closed')
  ,(4, 'Person5', '2018-04-23','Open')
  ,(4, 'Person5', '2018-04-25','Open')

select *
from @T1 t2
where TransactionDate <= '2018-12-31'
order by ClaimID, TransactionDate

Output will look like this:

I need to create a Boolean value column is_Open. For example Person2on a last TransactionDate had Status "Closed", so value for this row and all previous rows for Person2 should be 0.

Same for Person3 - its been closed on 2018-03-23, so all previous for Person3 should be 0.

But Person5 has Status "Open" on the last TransactionDate, so the value for this (last transactionDate) should be 1 but for the previous TransactionDate should be 0 even if status is Open

For column is_Open:

If the last TransactionDate for Person has Status Open, then 1, all previous transactions 0. If the last transaction has Status Closed, then 0, and all previous transactions 0.

For column is_Closed:

If the last TransactionDate for Person has Status Closed then 1 ,all previous transactions 0.If the last TransactionDate for Person has Status Open, then 0 and all previous transactions 0.

The result should look like this:

I tried using ROW_NUMBER() and LAST_VALUE functions, but so far couldn't figure out:

select
    *,
    row_number() over (partition by  ClaimID order by TransactionDate) as rowNum,
    iif(last_value(Status) over (partition by Person order by TransactionDate desc) = 'Closed', 1, 0) as LastValue
from 
    @T1 t2
where 
    TransactionDate <= '2018-12-31'
order by 
    ClaimID, TransactionDate

Answer 1

I believe this would be the desired result isOpen checks the next row for the particular customer and if there is an open or closed status - closes the current row, same thins is happening with the isClosed , however in the opposite logic :

select *,case 
    when ClaimStatus = 'Closed' then 0
    when lead(ClaimStatus) over(partition by ClaimantID order by transactiondate asc) = 'Closed' 
        or lead(ClaimStatus) over(partition by ClaimantID order by transactiondate asc) = 'Open' then 0
    else 1
 end as is_Open
     from @T1

Also, this way is simplier and gives the same outcome. Correct me if I am wrong:

declare @T1 table 
            (
                ClaimID int, 
                Claimant varchar(20), 
                TransactionDate date,
                ClaimStatus varchar(20)
            )

insert into @T1 
values 
   (1, 'Claimant1', '2018-03-05','Open')
  ,(1, 'Claimant1', '2018-02-05','Open')
  ,(1, 'Claimant2', '2018-03-10','Open')
  ,(1, 'Claimant2', '2018-03-15','Closed')
  ,(2, 'Claimant3', '2018-03-15','Open')
  ,(2, 'Claimant3', '2018-03-23','Closed')
  ,(3, 'Claimant4', '2018-04-18','Closed')
  ,(4, 'Claimant5', '2018-04-23','Open')
  ,(4, 'Claimant5', '2018-04-25','Open')
  ,(5, 'Claimant6', '2018-05-18','Open')
  ,(5, 'Claimant6', '2018-05-23','Open')

select *,
iif(ROW_NUMBER() OVER(PARTITION BY Claimant ORDER BY TransactionDate desc) = 1 and ClaimStatus = 'Open',1,0) AS is_Open
from @T1 t2
where TransactionDate <= '2018-12-31'
order by ClaimID, TransactionDate