Avoiding computed columns code duplication in SQL Server SELECT statement

I am trying to build a SELECT query which I will eventually turn into a view, once finished, where I am selecting a bunch of columns from a Table and then have a series of CASE statements to populate a calculated column. However, I am a novice with SQL and so not sure the best way to go about this.

I am calculating date difference to plug into each case statement at the moment and have found myself repeating the same date diff calculation within each CASE statement and coming from an OOP background, this is not DRY and feels like it can be improved. Please see below for a code snippet.

SELECT TOP 1000
   Col1
   Col2
   CASE
      WHEN t.Col1 = 'X' and DATEDIFF(month, try_convert(datetime, try_convert(varchar(8), 
      YYYYMMDD)), getdate())/12 <= 5 then 'Not Included'
      WHEN t.Col1 = 'X' and DATEDIFF(month, try_convert(datetime, try_convert(varchar(8), 
      YYYYMMDD)), getdate())/12 > 5 then 'Included'
      WHEN t.Col1 = 'X' and DATEDIFF(month, try_convert(datetime, try_convert(varchar(8), 
      YYYYMMDD)), getdate())/12 IS NULL then 'Included'
   END AS List
from db.Table t

The above is the general structure I have gone for thus far, but the end product will have 6 more case statements, all including the same DATEDIFF calc. Can anyone provide some tips in making this more DRY and reducing code replication?

EDIT: The YYYYMMDD part refers to an integer value that I am trying to convert to a date, in order to calculate the year difference.

Many thanks!

Answer

You need to add 1 select statement layer.

Check following way.

 select Col1,Col2,
CASE
      WHEN m.Col1 = 'X' and m.Value <= 5 then 'Not Included'
      WHEN m.Col1 = 'X' and m.Value > 5 then 'Included'
      WHEN m.Col1 = 'X' and m.Value IS NULL then 'Included'
   END AS List
 from 
(
select Col1,Col2, DATEDIFF(month, try_convert(datetime, try_convert(varchar(8), 
      YYYYMMDD)), getdate())/12 as Value
from db.Table t
) m

Advertisement

Answer