SQL Server : creating derived column

The following is the sample data for the table that I'm working. I looking to create a new column RenIndicator that is having 3 distinct values and this should be populated based on NextPolicy column and then Expiry date column.

Any thoughts will be good. Included the sample data and my expects results below.

New column RenIndicator and it has one of three possible values: Yes, No, N/a yet.

Yes - When the NextPolicy has a policynumber & Expiry date is the before the current date

No - When the NextPolicy column does not have any value & the Expiry date is less than the current date

N/A yet - When the NextPolicy column does not have any value & the Expiry date is greater than the current date

I'm looking query more like this

                select a.*, 
                        case when a.NextPolicy <> NULL and NewRenewal = 'New' then 'Renewal' ELSE
                         when a.NextPolicy = NULL and ExpiryDate > GETDATE() then 'Not Availble Yet' ELSE
                        when a.NextPolicy = NULL and ExpiryDate < GETDATE() then 'Not Availble Yet' 
                        end as 'Renewal Indicator'  
                        from final a

Sample data:

--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable') IS NOT NULL
    DROP TABLE #mytable

--===== Create the test table with 
CREATE TABLE #mytable 
(
    Region nvarchar(300), 
    NextPolicy nvarchar(300),
    CurrentPolicyNumber nvarchar(300),
    PolicyNumber nvarchar(100), 
    NewRenewal nvarchar(100), 
    EffectiveDate Date, 
    ExpiryDate Date, 
    Coverage nvarchar(100), 
    InsuredName nvarchar(300)
)

SET DATEFORMAT DMY

--===== Insert the test data into the test table
INSERT INTO #mytable (Region, NextPolicy, CurrentPolicyNumber, PolicyNumber, NewRenewal, EffectiveDate, ExpiryDate, Coverage, InsuredName)
    SELECT 'Asia', '47-ACA-000001-02', '47-ACA-000001-01', '000001', 'New', '2016-12-25', '2017-12-25', '', 'CPC Corporation, Taiwan (CPC)' 
    UNION ALL
    SELECT 'Asia', '', '47-ACA-000001-02', '000001', 'Renewal', '2017-12-25', '2018-12-25', '', 'CPC Corporation, Taiwan (CPC)' 
    UNION ALL
    SELECT 'North America', '42-XPR-000001-02', '42-PRP-000001-01', '000001', 'New', '2013-05-15', '2014-05-15', 'PRP', 'AvalonBay Communities,Inc.' 
    UNION ALL
    SELECT 'North America', '42-XPR-000001-03', '42-XPR-000001-02', '000001', 'Renewal', '2014-05-15', '2015-05-15', 'XPR', 'AvalonBay Communities, Inc.' 
    UNION ALL
    SELECT 'North America', '42-XPR-000001-04', '42-XPR-000001-03', '000001', 'Renewal', '2015-05-15', '2016-05-15', 'XPR', 'AvalonBay Communities, Inc.' 
    UNION ALL
    SELECT 'North America', '', '42-XPR-000001-04', '000001', 'Renewal', '2016-05-15', '2017-05-15', 'XPR', 'AvalonBay Communities, Inc.' 
    UNION ALL
                     SELECT 'Asia',         '47-ABA-000001-02','47-ABA-000001-01','000001','New',       '2015-11-25',   '2016-11-25','','Taiwan' UNION ALL
                     SELECT 'Asia',         '','47-ABA-000001-02','000001','Renewal','2016-11-25','2017-11-25','','Taiwan'

Solution

This can be done using a case statement:

SELECT *
     , CASE WHEN NextPolicy <> '' AND ExpiryDate < CAST(GETDATE() AS DATE) THEN 'Yes'
            WHEN NextPolicy = '' AND ExpiryDate < CAST(GETDATE() AS DATE) THEN 'No'
            WHEN NextPolicy = '' AND ExpiryDate >  CAST(GETDATE() AS DATE) THEN 'N/a yet'
       END AS RenIndicator
FROM #mytable

I've used the empty string '' for the "does not have any value" requirement, but if you have NULLS in those fields you will need to do something like ISNULL(NextPolicy, '') in the CASE to account for them.

We are assuming that any value is a valid policy number as well. Additional data validation could be included as needed.

Also upcoming edit pending more information on what happens when the ExpiryDate is today's date.