Use result of Case statement in another Case statement

Viewed 11923

I have quite a long SELECT query but I have pasted the relevant part here.

I need to use the result of the of my CASE statement to use in another CASE statement. I'm doing this in SQL Server.

Would be very grateful for help.

SELECT
    CompanyContact.Name AS CompanyName,
    CASE 
       WHEN SUBSTRING(HeadLease.TenantBreakNotice, LEN(HeadLease.TenantBreakNotice), 1) = 'M'
          THEN CONVERT(VARCHAR(10), DATEADD(DD, -365 / (12 / SUBSTRING(HeadLease.TenantBreakNotice, 1, LEN(HeadLease.TenantBreakNotice) - 1)), HeadLease.TenantBreakDate), 103)
       WHEN SUBSTRING(HeadLease.TenantBreakNotice, LEN(HeadLease.TenantBreakNotice), 1) = 'Y'
          THEN CONVERT(VARCHAR(10), DATEADD(DD, -365 * (SUBSTRING(HeadLease.TenantBreakNotice, 1, LEN(HeadLease.TenantBreakNotice) - 1)), HeadLease.TenantBreakDate), 103)
       ELSE HeadLease.TenantBreakNotice
    END AS [TenantBreakNotice],  <-- I need this to be used in the case statement below.
    CASE
       WHEN [TenantBreakNotice] < CONVERT(varchar(10), getdate(), 103)  
          THEN 'Expiry' 
       WHEN [TenantBreakNotice] IS NULL 
          THEN 'Expiry' 
       ELSE 'Break' 
    END AS [LeaseEventType]
FROM 
    HeadLease  
4 Answers
Related