CODEWITHSUNDEEP
sqlsql-server
Xin
Xin

Reputation: 674

How to simplify code to distinguish date column by month range and add values to new column

I have a date column and essentially the logic I have currently is if the month and date are between Jan 1st and May 31st then I want the new column to show 'A', if it's June 1st - Dec 31st then B. Below is what I have and I was wondering what other ways/functions could I use to shorten my code while getting the same results? The reason is that there may be additional logic that could be added in the future and I want it to be as short as possible, thanks in advance!

SELECT TOP (10)
catch_date,
CASE 
    WHEN DATEPART(month, catch_date)  >= '1' AND DATEPART(month, catch_date)  <= '6' THEN 'A' 
    WHEN DATEPART(month, catch_date)  >= '6' AND DATEPART(month, catch_date)  <= '12' THEN 'B' 
    ELSE 'None'
    END AS 'Result'
FROM table

Result:

catch_date  Result
2018-09-09  B
2014-03-24  A
2016-04-04  A
2019-07-27  B
2017-04-06  A
2017-04-22  A
2014-03-05  A
2020-08-20  B
NULL        None
2013-07-04  B
``

Upvotes: 0

Views: 21

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

One method is to simplify the case expression:

(CASE WHEN MONTH(catch_date) < 6 THEN 'A'
      WHEN catch_date IS NOT NULL THEN 'B' 
      ELSE 'None'
 END) AS Result

Notes:

  • The function MONTH() is simpler than DATEPART().
  • Both DATEPART() and MONTH() return numbers, so the comparison should be to a number.
  • You don't need to compare a range. The CASE expressions are evaluated in order.
  • Don't use single quotes for column aliases.

You can write inscrutable code using something like:

select coalesce( substring('ABB', 1 + month(catch_date) / 6, 1), 'None') as result

But I recommend the clearer case expression.

Upvotes: 2

Related Questions