group date range with specific range

问题:

I know there's lots of grouping in date range. I've looked in the internet. But the one's I saw was hard wired range. Say,

CASE
   WHEN [Date] BETWEEN '2014-1-1' AND '2014-3-30' THEN 1
   WHEN [Date] BETWEEN '2014-4-1' AND '2014-6-30' THEN 2
   -- And so on
END

That only covers the Year 2014, right? But what if I need to also cover for the next 4 years or so. Do I really need to manually put it in the query? Or is there a way for it to cover for the next coming years?

In specific, I need to group the records by range. Aug to Jan (group 1) and Feb to July (group 2)

Any help would be greatly appreciated. Thanks!

回答1:

Use MONTH()

CASE
  WHEN month([Date]) BETWEEN 2 AND 7 THEN 2
  ELSE 1
END

回答2:

group by 
         case when Month(DateCol) IN (8, 9, 10, 11, 12, 1) then 1 
              else 2 
         end

Ref: Month()