SQLTeam.com | Weblogs | Forums

How to rank quarters

i have a table in which I have a column named Month which is filled based on the date in which I make the refresh (so its filling dynamically) and based on this date I calculate the quarter and the fiscal year (FY). The quarter starts from November and it has 3 months(nov, dec and jan is Q1; feb, mar apr is Q2 and so on). I managed to do this. My issue is that I need to rank these quarters based on the refresh date (the day in which I make the refresh).

i wrote this code but its not working:

select [Month],[Quarter],[FY],
rank() over (partition by [FY] order by [FY] desc )as[Rank_Quarter]
[dbo].[Calendar CMD]

it fills the entire column=Rank_Quarters with 1

this is my example:

In table 1 i have the row data.
if I make the refresh in 2020-11, 2020-12 or 2021-01 (which is nov dec or jan) i need to fill in the ranking for the last previous 4 quarters. So being in Q1 it means that the last 4 quarters were from 2019-11 to 2020-10) table2
if I make the refresh in Q2 (febr or march or apr) then I need to rank from 2020-02 until last 2021-01) table3
So, the bottom line is that based on the refresh date, I have to rank the last 4 closed quarters.

i hope I explained well and maybe someone could help me. i would really appreciate :slight_smile:

If you provide some simple DDL, sample data and expected output, we can help. The picture helps, but I don't want to guess at what you have.

Sorry i forgot to give a sample. As for the output I gave 3 samples from table2 or table3 or table4 but it depends on the refresh date (the date in which I make the refresh) So lets say that I make the refresh today, the Corelated Month column would be 2021-01 (January 2021) which is in quarter 1 (Q1), so I need to rank the past completed 4 quarters like is shown in table2. If I make the refresh in April then I'm in the Q2 Fiscal year 2021, so I need to rank the former 4 quarters (Q1 fy21. Q4FY20, Q3FY20 and Q2 like is in the table 3 etc.

the sample data:

insert into [dbo].Table

thank you for your support

I don't understand the tables 2-4 in your screen shots. Are they all the same table??. I have the data you provided, but I don't understand the ranking and previous 4 quarters. Let's pick one date Jan 2021.(2021-01). What are we looking back at and what are the other 3 tables?


IF OBJECT_ID('tempdb..#Table') IS NOT NULL 
    DROP TABLE #Table

create table #table
(Month1 char(7),
 Quarter1 char(2),
 FY char(7),
 RankQuarter tinyint)

insert into #table

the table2, table3 and table 4 are 4 scenarios, in red i put an example of the refresh date.
in my company the fiscal year starts with November. So Nov 2020 means the start of fiscal year 21 so it is Q1fy21. Every quarter has 3 months starting with Nov.
Table 1 is the raw data before filling the Rank Column. Table2,3 4 are 3 examples and in red is a random data to make an example.
In my script i took a variable
declare @refreshdate date
set @refreshdate=getdate()
If today is 8 january 2021, based on the variable the month column will be calculated, also the Quarter and FY columns. So if the @refreshdate=8 january, the Rank_Quarter column should be filled as is it in table2.

If lets say that i make the refresh in May, then the @refreshdate is a date in May, it means that my Quarter will be in Q3 so the ranking should start with Q2fy21 Q1fy21, Q4fy20 and Q3fy20.