Use result of Case statement in another Case statement

Question

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  

Solution 1

You cannot use a column alias in the same select where it is defined. The usual solution is to repeat the logic (hard to maintain), use a subquery, or CTE. SQL Server offers another elegant solution:

SELECT hl.Name AS CompanyName, v.TenantBreakNotice,
       (CASE WHEN v.TenantBreakNotice < CONVERT(varchar(10), getdate(), 103)  THEN 'Expiry' 
             WHEN TenantBreakNotice IS NULL THEN 'Expiry' 
             ELSE 'Break' 
        END) AS [LeaseEventType]

FROM HeadLease hl OUTER APPLY
     (VALUES (CASE WHEN SUBSTRING(hl.TenantBreakNotice, LEN(hl.TenantBreakNotice), 1) = 'M'
                   THEN CONVERT(VARCHAR(10), DATEADD(DAY, -365/(12/SUBSTRING(hl.TenantBreakNotice, 1, LEN(hl.TenantBreakNotice) -1)), hl.TenantBreakDate), 103)
                   WHEN SUBSTRING(hl.TenantBreakNotice, LEN(hl.TenantBreakNotice), 1) = 'Y'
                   THEN CONVERT(VARCHAR(10), DATEADD(DAY, -365*(SUBSTRING(hl.TenantBreakNotice,1, LEN(hl.TenantBreakNotice)-1)), hl.TenantBreakDate), 103)
                   ELSE hl.TenantBreakNotice
              END) v(TenantBreakNotice);

Of course, the logic is incorrect, because you are comparing dates as strings. However, that is something you need to figure out yourself. Don't convert dates to strings for date operations. And, you should output the results as YYYY-MM-DD so the formats are unambiguous.

Solution 2

As @Juergen pointed out, you can't do exactly what you want, but you could compute the first CASE expression in a subquery, and then use it an outer wrapping query:

WITH cte AS (
    SELECT
        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]
    FROM HeadLease
)

SELECT
    Name,
    TenantBreakNotice,
    CASE WHEN TenantBreakNotice < CONVERT(varchar(10), getdate(), 103)
         THEN 'Expiry'
         WHEN TenantBreakNotice IS NULL THEN 'Expiry' 
         ELSE 'Break'
    END AS [LeaseEventType]
FROM cte;

Solution 3

Use CTEs (common table expressions). In CTEs you can refer to the columns from the previous CTE, so you can split the CASE logic like you would like.

Example:

WITH 
    CTE_1 AS 
    (
        SELECT
            *
            ,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]
        ...
    ),

    CTE_2 AS
    (
        SELECT
            *    
            ,CASE
                WHEN [TenantBreakNotice] < CONVERT(varchar(10),getdate(),103)  THEN  'Expiry' 
                WHEN [TenantBreakNotice] IS NULL THEN 'Expiry' 
                ELSE 'Break' 
            END AS [LeaseEventType]
        FROM
            CTE_1
    )

SELECT * FROM CTE_2

Solution 4

You can move the first case expression into your from by using a derived table/subquery like so:

select
     cc.Name as CompanyName  
   , convert(varchar(10),hl.[TenantBreakNotice],103) as TenantBreakNotice 
   , case
      when hl.[TenantBreakNotice] < getdate() then  'Expiry' 
      when hl.[TenantBreakNotice] is null then 'Expiry' 
      else 'Break' 
      end as [LeaseEventType]
from (
    select *, 
        case 
          when substring(HeadLease.TenantBreakNotice,len(HeadLease.TenantBreakNotice),1) = 'M'
               then dateadd(day,-365/(12/substring(HeadLease.TenantBreakNotice,1,len(HeadLease.TenantBreakNotice)-1)),HeadLease.TenantBreakDate)
          when substring(HeadLease.TenantBreakNotice,len(HeadLease.TenantBreakNotice),1) = 'Y'
               then dateadd(day,-365*(substring(HeadLease.TenantBreakNotice,1,len(HeadLease.TenantBreakNotice)-1)),HeadLease.TenantBreakDate)
          else HeadLease.TenantBreakNotice
        end as [TenantBreakNotice]
    from HeadLease
    ) as hl
  inner join CompanyContact cc
    on cc....

Notes:

• Don't compare strings as dates, this is going to lead to problems; especially with the style you specified (103).
• Bad Habits to Kick : Using shorthand with date/time operations - Aaron Bertrand
• Bad habits to kick : mis-handling date / range queries - Aaron Bertrand

View more solutions