Assign number of quarter over years on SQL condition

Question

I'm working on quarter counter that would be recognizing data gaps by quarter.

Here is my data:

Year  Quarter  Department
2017  1        A 
2017  2        A
2017  3        A
2017  4        A

2018  1        A
2018  2        A

I'm trying to assign number of quarter that would reference from current_date value. While Department dropes out from the table, quarter count remains unchanged by that scenario.

Year  Quarter  Department  Quarter_count
2017  1        A           12
2017  2        A           11
2017  3        A           10
2017  4        A           9

2018  1        A           8
2018  2        A           7
2018  3        A           6 THIS RECORD DOESN'T EXIST
2018  4        A           5 THIS RECORD DOESN'T EXIST

2019  1        A           4
2019  2        A           3
2019  3        A           2
2019  4        A           1

My starting code is:

SELECT Department, Year, Quarter,
       ROW_NUMBER() OVER (ORDER BY Year, Quarter ASC)

FROM TABLE_A
ORDER BY Depratment, Year, Quarter ASC

You will need Quarters table either static or built dynamically. You will calculate sequential quarter number there. Then LEFT JOIN to your "real" data. — PM 77-1

Simeon Pilgrim Simeon Pilgrim · Accepted Answer · 2019-12-09T22:02:55

So this is rather simple math, there are four quarters per year, so difference of years times four, minus the quarters of the start year and add the quarters of the current date, plus one as we are not zero based..

thus in expanded form:

with table_a as (
SELECT column1 as year, column2 as Quarter, column3 as Department from values 
  (2017,1,'A'),(2017,2,'A'),(2017,3,'A'),(2017,4,'A')
  ,(2018,1,'A'),(2018,2,'A')
  --,(2018,3,'A'),(2018,4,'A')
  ,(2019,1,'A'),(2019,2,'A'),(2019,3,'A'),(2019,4,'A')
)
SELECT department, year, quarter
    ,YEAR(current_date) as cdy
    ,QUARTER(current_date) as cdq
    ,(cdy-year)*4 as year_diff_in_q
    ,year_diff_in_q - quarter + cdq + 1 as Quarter_count
FROM table_a
ORDER BY department, year, quarter ASC;

gives:

DEPARTMENT  YEAR    QUARTER CDY CDQ YEAR_DIFF_IN_Q  QUARTER_COUNT
A   2017    1   2019    4   8   12
A   2017    2   2019    4   8   11
A   2017    3   2019    4   8   10
A   2017    4   2019    4   8   9
A   2018    1   2019    4   4   8
A   2018    2   2019    4   4   7
A   2019    1   2019    4   0   4
A   2019    2   2019    4   0   3
A   2019    3   2019    4   0   2
A   2019    4   2019    4   0   1

thus smaller:

with table_a as (
SELECT column1 as year, column2 as Quarter, column3 as Department from values 
  (2017,1,'A'),(2017,2,'A'),(2017,3,'A'),(2017,4,'A')
  ,(2018,1,'A'),(2018,2,'A')
  --,(2018,3,'A'),(2018,4,'A')
  ,(2019,1,'A'),(2019,2,'A'),(2019,3,'A'),(2019,4,'A')
)
SELECT department, year, quarter
    ,((YEAR(current_date)-year)*4) - quarter + QUARTER(current_date) + 1 as Quarter_count
FROM table_a
ORDER BY department, year, quarter ASC;

giving:

DEPARTMENT  YEAR    QUARTER QUARTER_COUNT
A   2017    1   12
A   2017    2   11
A   2017    3   10
A   2017    4   9
A   2018    1   8
A   2018    2   7
A   2019    1   4
A   2019    2   3
A   2019    3   2
A   2019    4   1

Assign number of quarter over years on SQL condition

2 Answers