CODEWITHSUNDEEP
sql-servert-sqlcount
Serdia
Serdia

Reputation: 4428

How can I check the last transaction date for each ClaimNumber and get the value of 1 if its status is 'Open'

I have a PolicyNumber that can have multiple ClaimNumber. Same ClaimNumber can have different multiple statuses (Open or Closed). I want to pick the status based on top last TransactionDate. And if its 'Open' then in a new column put value of 1. Rest of it should be 0.

select  PolicyNumber, 
        ClaimNumber, 
        ClaimantNumber, 
        ReserveStatus, 
        TransactionDate ,
/*  I tried something like this but it doesnt give me correct result */
    CASE 
        WHEN  ROW_NUMBER() OVER (PARTITION BY PolicyNumber, ClaimNumber ORDER BY TransactionDate desc  )=1 THEN 1 ELSE 0 END as OpenY/N
from RockhillClaimsDataFeed_PBI 
where ClaimNumber = 'CACA0000000117'
order by PolicyNumber

The goal is to calculate only open claims. As you can see an a sample below - out of all ReserveStatus I only need 1 where the last top TransactionDate is

enter image description here

EDIT:

Kannan Kandasamy if I do this query:

select  PolicyNumber, 
        ClaimNumber, 
        ClaimantNumber, 
        ReserveStatus, 
        TransactionDate ,
        [Open y/n] = case when ReserveStatus='Open' and Row_number() over(partition by policyNumber, ClaimNumber order by TransactionDate desc) = 1 then 1 else 0 end
from RockhillClaimsDataFeed_PBI 
where ClaimNumber = 'CACA0000000117'

Then column Open y/n displays all 0 The dates 2/21/2017 would be the correct ones. So I need to grab the ReserveStatus of the one of those dates. The top one. And mark 1 in a column Open y/n

enter image description here

LONG

Thanks. But I dont have a column [Open Y/N] in my dataset. I modified your query but it says Incorrect syntax near 'CASE'

WITH ABC
AS
(
select  PolicyNumber, 
        ClaimNumber, 
        ReserveStatus, 
        TransactionDate,
        --[Open Y/N],
        Row_number() over(partition by policyNumber, ClaimNumber order by TransactionDate desc) as Indicator
from RockhillClaimsDataFeed_PBI
)
Update ABC
SET ABC.Indicator = 1 CASE WHEN ABC.ReserveStatus = 'Open' THEN '1' ELSE '0'
WHERE ABC.Indicator = 1

Upvotes: 0

Views: 507

Answers (2)

LONG
LONG

Reputation: 4620

WITH ABC
AS
(
select  PolicyNumber, 
        ClaimNumber, 
        ReserveStatus, 
        TransactionDate,
        OtherExistingColumn,
        Row_number() over(partition by policyNumber, ClaimNumber order by TransactionDate desc) as Indicator
from RockhillClaimsDataFeed_PBI
)
Update ABC
SET ABC.OtherExistingColumn =  CASE WHEN ABC.ReserveStatus = 'Open' THEN '1' ELSE '0' END
WHERE ABC.Indicator = 1

Upvotes: 0

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13969

You can use row_number and get this as below:

select  PolicyNumber, 
        ClaimNumber, 
        ClaimantNumber, 
        ReserveStatus, 
        TransactionDate ,
        [Open y/n] = case when ReservceStatus='Open' and Row_number() over(partition by policyNumber, ClaimNumber order by CONVERT(date,TransactionDate) desc) = 1 then 1 else 0 end
from RockhillClaimsDataFeed_PBI

Upvotes: 1

Related Questions