0

New to the forum. I am looking to use a CASE WHEN function based on two separate fields . Those fields are order day and Month . Month is a calculation from:

DATEPART (Month, order_day ) AS Month

The code I have is below, but not getting the desired result. Want to say when order day is between x dates, then have the data in the Month column 1, otherwise give me the normal data that appears in the Month column.

when order_day between to_date ('20201227', 'YYYYMMDD') and TO_DATE  ('20201231', 'YYYYMMDD') THEN Month = '1'
else Month End As Month2
M-Chen-3
  • 2,036
  • 5
  • 13
  • 34
Alec Newbie
  • 11
  • 3
  • Take a look at https://stackoverflow.com/questions/6042767/sql-server-case-when-in-statement – Little Santi Apr 17 '21 at 00:16
  • [Why should I tag my DBMS](https://meta.stackoverflow.com/questions/388759/) –  Apr 19 '21 at 07:48
  • Why the hard-coded context of the from/to dates? and your "else Month end" part of the case/when is confusing. Do you mean to grab the month representation from the "Order_Day" column? What happens if you change your query in a few months and looking for activity between the month March 8 - March 27? Does that become 1 and everything else become 3 (March is 3rd month of year?) ? Please edit your original question and revise clarification and context why case/when. – DRapp Apr 19 '21 at 08:59

1 Answers1

0

Not easy to understand the question, but your case when in a SELECT statement should look like this:

SELECT 
  case when order_day between to_date ('20201227', 'YYYYMMDD') and TO_DATE('20201231', 'YYYYMMDD')
    then Month = '1' 
    else Month 
  end
James
  • 2,954
  • 2
  • 12
  • 25
  • The error was caused by repeating the "month" column name after the THEN part of the argument.. – Alec Newbie Apr 17 '21 at 01:18
  • Once I learn how to resolve this thread I will – Alec Newbie Apr 17 '21 at 01:19
  • Even your query case/when does no make sense. Your use case/when I suspect would fail as "Month" is a probably a function name, not a column. They were resulting in a final column name of "Month2". Their question is not exactly clear. – DRapp Apr 19 '21 at 08:57