SQL Script to get claim re-open date

sujith kumar matharasi 351 Reputation points
2022-10-25T18:28:25.31+00:00

Hi All,

I have a SQL table with the below script:

Create Table dbo.ClaimInfo  
(  
  
ClaimID Int,  
ClaimOpenDate Date,  
StatuschangeDate Date,  
ClaimDetails nvarchar(max),  
PaidAmount Int,  
ClaimClosedDate Date,  
ClaimReopenDate Date  
)  
  
Insert Into dbo.claiminfo(1,'2021-01-01','2021-01-01','Claim Created',0,NULL,NULL)  
Insert Into dbo.claiminfo(1,'2021-01-01','2021-01-02','Claim Paid',100,NULL,NULL)  
Insert Into dbo.claiminfo(1,'2021-01-01','2021-01-03','Claim Closed',100,'2021-01-03',NULL)  
Insert Into dbo.claiminfo(1,'2021-01-01','2021-01-04','Claim Reopened'100,'2021-01-03',NULL)  
Insert Into dbo.claiminfo(1,'2021-01-01','2021-01-05','Claim Closed'100,'2021-01-05',NULL)  
Insert Into dbo.claiminfo(1,'2021-01-01','2021-01-06','Claim Reoepened'100,'2021-01-05',NULL)  

So if we look at the above data ClaimNumber 1 is re-opened on 2021-01-04 & 2021-01-06 so i want to add a new column to this table called ClaimReopenDate and the output of that column should be like this

1,'2021-01-01','2021-01-01','Claim Created',0,NULL,NULL  
 1,'2021-01-01','2021-01-02','Claim Paid',100,NULL,NULL  
1,'2021-01-01','2021-01-03','Claim Closed',100,'2021-01-03',NULL  
 1,'2021-01-01','2021-01-04','Claim Reopened'100,'2021-01-03','2021-01-04'  
1,'2021-01-01','2021-01-05','Claim Closed'100,'2021-01-05','2021-01-04'  
1,'2021-01-01','2021-01-06','Claim Reoepened'100,'2021-01-05','2021-01-06'  

So if we look at the output the claim wasnt closed until 2021-01-03 so the reopen date should be NULL, but from 2021-01-04 when the claim is first reopened until 2021-01-05 the next closure it should display the latest reopendate and again as the claim is reopened on 2021-01-06 it should display the latest reopen date.

So basically the reopen date should be NULL until first reopen and after that it should display the latest one based on the claimdetails for that row.

To acheive this we can use the claimdetails like '%Reopened%' and use the statuschangedate as reopen date.

I am kind of stuck with no idea on how to approach this, Can someone please help me with any suggestions on this.

Thanks in advance for all your help

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,114 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,582 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Jingyang Li 5,891 Reputation points
    2022-10-25T21:07:22.907+00:00
    Create Table dbo.ClaimInfo  
     (  
          
     ClaimID Int,  
     ClaimOpenDate Date,  
     StatuschangeDate Date,  
     ClaimDetails nvarchar(max),  
     PaidAmount Int,  
     ClaimClosedDate Date,  
     ClaimReopenDate Date  
     )  
          
     Insert Into dbo.claiminfo values (1,'2021-01-01','2021-01-01','Claim Created',0,NULL,NULL)  
    ,(1,'2021-01-01','2021-01-02','Claim Paid',100,NULL,NULL)  
    ,(1,'2021-01-01','2021-01-03','Claim Closed',100,'2021-01-03',NULL)  
    ,(1,'2021-01-01','2021-01-04','Claim Reopened',100,'2021-01-03',NULL)  
    ,(1,'2021-01-01','2021-01-05','Claim Closed',100,'2021-01-05',NULL)  
    ,(1,'2021-01-01','2021-01-06','Claim Reopened',100,'2021-01-05',NULL)  
     ;with mycte as (select c.*,  
     SUM(Case when ClaimDetails like  '%Reopened%' then 1 else 0 end)   
     Over(Partition by  ClaimID Order by    StatuschangeDate ) grp  
     from   dbo.ClaimInfo c)  
      
     select ClaimID,ClaimOpenDate,StatuschangeDate  
     ,ClaimDetails,PaidAmount   
     , Case when grp>0 then MIN(StatuschangeDate) Over(partition  by grp)    
      else null end ClaimReopenDate  
       
      from mycte  
        
      
     drop Table dbo.ClaimInfo  
    
    0 comments No comments

  2. Erland Sommarskog 104.1K Reputation points MVP
    2022-10-25T21:30:47.96+00:00
       SELECT *, (SELECT MAX(StatuschangeDate)  
                  FROM   ClaimInfo b  
                  WHERE  b.ClaimDetails = 'Claim Reopened'  
                    AND  b.StatuschangeDate <= a.StatuschangeDate) AS ReopenDate  
       FROM ClaimInfo a  
    

    By the way, it was create that you posted CREATE TABLE + INSERT. But I would appreciate if you test your script before you posted it. I had to correct a few errors.

    0 comments No comments

  3. sujith kumar matharasi 351 Reputation points
    2022-10-27T16:02:55.653+00:00

    Thanks both of you, it worked. Erland i will make sure to test the code from next time sorry about that