Avoiding computed columns code duplication in SQL Server SELECT statement

Question

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!

Use either a CTE or subquery to calculate the value. Although that expression is strange - why convert a date to a string then back to a date? If `YYYYMMDD` is a `date` field all you need to do is `datediff(month,yyyymmdd,getdate())`. If not, what is it? Why is it *not* a `date`? — Panagiotis Kanavos, Oct 14 '21 at 08:59
Hi Panagiotis, sorry I should've clarified that the thing I am converting is an integer value in the format of YYYYMMDD, so as far as my knowledge is aware, this is one way of converting it to the required format? — hs14428, Oct 14 '21 at 09:09
Why is this not a `date`? That's the correct, portable type, which allows you to use all date-related functions. It takes less space too. All conversions cause problems because they prevent the use of indexes. Using the wrong type allows storing invalid values too, which is probably why you use `TRY_CONVERT` instead of `cast` — Panagiotis Kanavos, Oct 14 '21 at 09:22
Why is the integer YYYYMMDD not a date? That's just the format the YYYYMMDD data that I am receiving is in unfortunately. I will try and work backwards and see if I can get the data provider to give it in date format and use your method as it seems more robust! Thank you — hs14428, Oct 14 '21 at 11:03
[date](https://learn.microsoft.com/en-us/sql/t-sql/data-types/date-transact-sql?view=sql-server-ver15) is a specific SQL Server data type, not a format. Strings in the form `YYYYMMDD` or `YYYY-MM-DD` can be converted to `date` easily. — Panagiotis Kanavos, Oct 14 '21 at 11:25
Does this answer your question? [Possible to store value of one select column and use it for the next one?](https://stackoverflow.com/questions/65818438/possible-to-store-value-of-one-select-column-and-use-it-for-the-next-one) — Charlieface, Oct 14 '21 at 13:24

Salman A · Answer 1 · 2021-10-14T13:41:03.140

2

I use CROSS APPLY for DRY. And I would rather subtract 5 years from current date then compare:

SELECT
    Col1,
    Col2,
    CASE WHEN Col1 = 'X' THEN
        CASE WHEN YYYYMMDD > refdate THEN 'Not Included' ELSE 'Included' END
    END
FROM t
CROSS APPLY (SELECT DATEADD(YEAR, -5, CURRENT_TIMESTAMP) AS refdate) AS ca

edited Oct 14 '21 at 13:41

answered Oct 14 '21 at 11:02

Salman A

262,204
82
430
521

score 1 · Accepted Answer · answered Oct 14 '21 at 08:33

1

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

answered Oct 14 '21 at 08:33

Malvik Bhavsar

407
5
8

Hi @Malvik Bhavsar, that works perfectly, thanks for you help! I'll definitely be using these extra select statements in the future. – hs14428 Oct 14 '21 at 09:47

Avoiding computed columns code duplication in SQL Server SELECT statement

2 Answers2