Using Month(Getdate()) with Between ... need help

• Tamrak

  SSC Eights!

  Points: 907

  More actions

  December 23, 2011 at 9:43 am

  #100430

  Hello,

  I have these existing codes:

  CASE WHEN Month(GetDate()) BETWEEN 2 AND 7 THEN 2

  ELSE 1 END AS FiscalCyle

  This one works, when the months fall between February to July.

  There are more modifications that I need to make with the following logics work:

  CASE WHEN Month(GetDate()) BETWEEN August 1st AND October 31st THEN 1

  WHEN Month(GetDate()) BETWEEN November 1st AND January 31st THEN 2

  WHEN Month(GetDate()) BETWEEN February 1st AND April 30th THEN 3

  WHEN Month(GetDate()) BETWEEN May 1st AND July 31st THEN 4

  Else ...........

  End as FiscalCycle

  (fiscal year starts in August - end of July)

  What can I do to ensure that, it happens from August through July (Chrologolically) ?

  Thanks.

• Jeff Moden

  SSC Guru

  Points: 1004589

  More actions

  December 23, 2011 at 4:09 pm

  #1425292

  Tamrak (12/23/2011)

  ---

  Hello,

  I have these existing codes:

  CASE WHEN Month(GetDate()) BETWEEN 2 AND 7 THEN 2

  ELSE 1 END AS FiscalCyle

  This one works, when the months fall between February to July.

  There are more modifications that I need to make with the following logics work:

  CASE WHEN Month(GetDate()) BETWEEN August 1st AND October 31st THEN 1

  WHEN Month(GetDate()) BETWEEN November 1st AND January 31st THEN 2

  WHEN Month(GetDate()) BETWEEN February 1st AND April 30th THEN 3

  WHEN Month(GetDate()) BETWEEN May 1st AND July 31st THEN 4

  Else ...........

  End as FiscalCycle

  (fiscal year starts in August - end of July)

  What can I do to ensure that, it happens from August through July (Chrologolically) ?

  Thanks.

  Add a "Year" component to sort on. Better yet, build a "Calendar" table.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)