How to create a columns based on other columns SQL Server 2012

Question

I have 2 tables @Claims and @ClaimsActivity:

Query:

declare @Claims table (ClaimID int)

insert into @Claims 
values (6070), (6080)

declare @ClaimsActivity table 
                        (
                            Activityid int, 
                            ClaimID int, 
                            Activity int, 
                            ActivityDate datetime, 
                            ClaimStatus int
                        )

insert into @ClaimsActivity 
values (1, 6070, 0, '2017-11-05 20:23:16.640', 0),
       (3, 6070, 6, '2017-11-06 13:50:28.203', 0),
       (4, 6070, 9, '2017-11-07 13:39:28.410', 0),
       (5, 6070, 10, '2017-11-07 13:40:49.980', 0),
       (7, 6070, 8, '2017-11-07 15:46:18.367', 1),
       (8, 6070, 8, '2017-11-07 16:50:49.543', 1),
       (9, 6070, 9, '2017-11-07 16:50:54.733', 0),
       (10, 6070, 4, '2017-11-07 16:55:22.135', 0),
       (11, 6070, 6, '2017-11-08 18:32:15.101', 0),
       (12, 6080, 0, '2017-11-12 11:15:17.199', 0),
       (13, 6080, 8, '2017-11-13 09:12:23.203', 1)

select * 
from @Claims

select * 
from @ClaimsActivity 
order by ActivityDate

I need to add 2 columns based on data in @ClaimsActivity: IsReopened and DateReopened

The logic is:

If the last ClaimStatus (based on ActivityDate) = 1 then IsReopened = 0
But if the last ClaimStatus = 0 then it need to go and check whether one of the Activity is = 9 (Claim Reopened)
and if one of the Activity = 9 then IsReopened should = 1 and DateReopened should be the last date when it was reopened

I brought column StatusOfClaim, but I also need IsReopened and DateReopened

select 
    Claimid,
    isnull((select top 1 
                case when al.ClaimStatus = 1 
                   then 'Closed' 
                   else 'Open' 
                end
            from
                @ClaimsActivity al 
            where 
                C.ClaimID = al.ClaimID 
            order by  
                al.ActivityDate desc), 'Open') as 'Status of Claim',
    NULL as 'isReopen',
    NULL as 'DateReopened'
from
    @Claims c

Desired output should be like this:

jtate · Accepted Answer · 2019-03-15 21:32:40Z

There are many different ways you can accomplish this, but here is an example using CROSS APPLY and OUTER APPLY:

SELECT 
    ClaimID,
    CASE WHEN tmp.IsOpen = 1 THEN 'Open' ELSE 'Closed' END AS 'Status of Claim',
    CASE WHEN tmp.IsOpen = 1 AND lastReopen.Activityid IS NOT NULL THEN 1 ELSE 0 END AS 'isReopen',
    lastReopen.ActivityDate AS 'DateReopened'
FROM @Claims c
    CROSS APPLY (
        SELECT ISNULL((
            SELECT TOP 1 CASE WHEN al.ClaimStatus = 1 THEN 0 ELSE 1 END 
            FROM @ClaimsActivity al 
            WHERE c.ClaimID = al.ClaimID 
            ORDER BY al.ActivityDate DESC
        ), 1) AS IsOpen
    ) tmp
    OUTER APPLY (
        SELECT TOP 1
            al.Activityid,
            al.ActivityDate
        FROM @ClaimsActivity al 
        WHERE c.ClaimID = al.ClaimID AND al.Activity = 9
        ORDER BY al.ActivityDate DESC 
    ) lastReopen

The CROSS APPLY is just used to produce a column that tells us whether a claim is open or closed, and we can reuse this throughout the rest of the query.

The OUTER APPLY is used to grab to the last "reopen" activity for each claim, of which you want the date.

I can't attest to the performance of this query, but this should at least give you the correct results.

Collectives™ on Stack Overflow

How to create a columns based on other columns SQL Server 2012

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related