All we need is an easy explanation of the problem, so here it is.
i have a column that has month number stored as integer. i would like to create a new column with the quater of each month. is there an simpler way of doing the same, i have to use this in multiple places. so just wanted to check if there is any simpler way of doing this.
currently i am using below to achieve it
case when tt1.resolved_month in (1,2,3) then 1 when tt1.resolved_month in (4,5,6) then 2 when tt1.resolved_month in (7,8,9) then 3 when tt1.resolved_month in (10,11,12) then 4 end as quater
i checked the Quarter function and it doesnt support number as input.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
CEIL function will get the quarter.
SELECT CEIL(resolved_month / 3) AS quarter
Month Month/3 Quarter ------------------------- 1 0.3333 1 2 0.6667 1 3 1 1 4 1.3333 2 5 1.6667 2 6 2 2 7 2.3333 3 8 2.6667 3 9 3 3 10 3.3333 4 11 3.6667 4 12 4 4
Instead of a
case expression, you could divide the month by 3 and
ceil the result:
SELECT CEIL(resolved_month / 3) AS quarter FROM tt1
As @exudong suggested in the comments, it may be a good idea to encapsulate this logic in a function and re-use it wherever needed.
You could use the
SELECT resolved_month, QUARTER(ADDDATE('2021-01-01',INTERVAL resolved_month-1 MONTH)) as quarter2 FROM months;
The advantage (😉) is that everybody who read this will recognize that the quarter is being calculated.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂