Custom SQL for quarter count starting from previous month

0
votes

I need to create a custom quarter calculator to start always from previous month no matter month, year we are at and count back to get quarter. Previous year wuarters are to be numbered 5, 6 etc

So the goal is to move quarter grouping one month back.

Assume we run query on December 11th, result should be:

YEAR  MNTH  QTR   QTR_ALT
2017    1   1      12
2017    2   1      12
2017    3   1      11
2017    4   2      11
2017    5   2      11
2017    6   2      10
2017    7   3      10
2017    8   3      10
2017    9   3      9
2017    10  4      9
2017    11  4      9
2017    12  4      8
2018    1   1      8
2018    2   1      8
2018    3   1      7
2018    4   2      7
2018    5   2      7
2018    6   2      6
2018    7   3      6 
2018    8   3      6
2018    9   3      5
2018    10  4      5
2018    11  4      5
2018    12  4      1
2019    1   1      1
2019    2   1      1
2019    3   1      2
2019    4   2      2
2019    5   2      2
2019    6   2      3
2019    7   3      3
2019    8   3      3
2019    9   3      4
2019    10  4      4
2019    11  4      4 

2019    12  4      THIS IS SKIPPED

Starting point is eliminating current_date so data end at previous month's last day

  SELECT DISTINCT 
     YEAR, 
     MNTH, 
     QTR 
  FROM TABLE
    WHERE DATA BETWEEN 
               (SELECT DATE_TRUNC(YEAR,ADD_MONTHS(CURRENT_DATE, -24))) AND 
               (SELECT DATE_TRUNC(MONTH,CURRENT_DATE)-1) 

  ORDER BY YEAR, MNTH, QTR
1
sqldatepartitioningsnowflake-cloud-data-platform
Why is 2019/11 sometimes in quarter 3 and sometimes in quarter 4? – Gordon Linoff
Error while writing the issue, will edit ;) – marcin2x4

1 Answers

1
votes

The following gets you all the dates you need, with the extra columns.

select to_char(add_months(a.dt, -b.y), 'YYYY') as year,
to_char(add_months(a.dt, -b.y), 'MM') as month,
ceil(to_number(to_char(add_months(a.dt, -b.y), 'MM')) / 3) as qtr,
ceil(b.y/3) as alt_qtr
from
(select trunc(sysdate, 'MONTH') as dt from dual) a,
(select rownum as y from dual connect by level <= 24) b;