how to rank the last 4 completed quarters in sql server
hello,
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 it's 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]
from
[dbo].[Calendar CMD]
it fills the entire column=Rank_Quarters with 1
this is my example:
image1538×693 24 KBIn 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
etc.
So, the bottom line is that based on the refresh date, I have to rank the last 4 closed quarters.
the sample data:
insert into [dbo].Table
values('2018-12','Q1','FY19/Q1','')
('2019-01','Q1','FY19/Q1','')
('2019-02','Q2','FY19/Q2','')
('2019-03','Q2','FY19/Q2','')
('2019-04','Q2','FY19/Q2','')
('2019-05','Q3','FY19/Q3','')
('2019-06','Q3','FY19/Q3','')
('2019-07','Q3','FY19/Q3','')
('2019-08','Q4','FY19/Q4','')
('2019-09','Q4','FY19/Q4','')
('2019-10','Q4','FY19/Q4','')
('2019-11','Q1','FY20/Q1','')
('2019-12','Q1','FY20/Q1','')
('2020-01','Q1','FY20/Q1','')
('2020-02','Q2','FY20/Q2','')
('2020-03','Q2','FY20/Q2','')
('2020-04','Q2','FY20/Q2','')
('2020-05','Q3','FY20/Q3','')
('2020-06','Q3','FY20/Q3','')
('2020-07','Q3','FY20/Q3','')
('2020-08','Q4','FY20/Q4','')
('2020-09','Q4','FY20/Q4','')
('2020-10','Q4','FY20/Q4','')
('2020-11','Q1','FY21/Q1','')
('2020-12','Q1','FY21/Q1','')
('2021-01','Q1','FY21/Q1','')
('2021-02','Q2','FY21/Q2','')
('2021-03','Q2','FY21/Q2','')
('2021-04','Q2','FY21/Q2','')
('2021-05','Q3','FY21/Q3','')
('2021-06','Q3','FY21/Q3','')
('2021-07','Q3','FY21/Q3','')
('2021-08','Q4','FY21/Q4','')
('2021-09','Q4','FY21/Q4','')
('2021-10','Q4','FY21/Q4','')
('2021-11','Q1','FY22/Q1','')
('2021-12','Q1','FY22/Q1','')
('2022-01','Q1','FY22/Q1','')
('2022-02','Q2','FY22/Q2','')
('2022-03','Q2','FY22/Q2','')
('2022-04','Q2','FY22/Q2','')
('2022-05','Q3','FY22/Q3','')
('2022-06','Q3','FY22/Q3','')
('2022-07','Q3','FY22/Q3','')
('2022-08','Q4','FY22/Q4','')
('2022-09','Q4','FY22/Q4','')
('2022-10','Q4','FY22/Q4','')
('2022-11','Q1','FY23/Q1','')
('2022-12','Q1','FY23/Q1','')
('2023-01','Q1','FY23/Q1','')
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 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 let's say that I make the refresh in May, then the @refreshdate is a date in May, which means that my Quarter will be in Q3 so the ranking should start with Q2fy21 Q1fy21, Q4fy20, and Q3fy20.
thank you for your support.
Best Answer
Don't over think this - all you need to do is work out the last 4 quarters prior to the current one.
I mocked up your table on my test system, but named it YourTable as Table is a bad name :)
I'm not sure what method you are using to turn dates into the month labels, but here's what I did:
declare @refreshdate date;
set @refreshdate=getdate(); -- this is 13 January 2021
declare @refreshmonth varchar(10);
set @refreshmonth = datename(year, @refreshdate)+'-'+right('00'+cast(datepart(month, @refreshdate) as varchar),2);
select @refreshmonth;
----------
2021-01
(1 row affected)
now if I subtract 1 quarter from this @refreshdate, using the standard date functions in SQL I can get a date in the last quarter
select dateadd(quarter, -1, @refreshdate); ---------- 2020-10-13 (1 row affected)
and then turn that into a Month as above
select dateadd(quarter, -1, @refreshdate), datename(year, dateadd(quarter, -1, @refreshdate))+'-'+right('00'+cast(datepart(month, dateadd(quarter, -1, @refreshdate)) as varchar),2);
---------- ---------------------------------
2020-10-13 2020-10
(1 row affected)
and I can do that for -2, -3 and -4 quarters
---------- --------------------------------- 2020-10-13 2020-10 2020-07-13 2020-07 2020-04-13 2020-04 2020-01-13 2020-01
with that result set I can then query onto my base table to get the FYs that match those Months, and at the same time, give them a row number
select FY, row_number()over(order by [Month] desc)
from dbo.YourTable
where
([Month]=datename(year, dateadd(quarter, -1, @refreshdate))+'-'+right('00'+cast(datepart(month, dateadd(quarter, -1, @refreshdate)) as varchar),2)
or [Month]=datename(year, dateadd(quarter, -2, @refreshdate))+'-'+right('00'+cast(datepart(month, dateadd(quarter, -2, @refreshdate)) as varchar),2)
or [Month]=datename(year, dateadd(quarter, -3, @refreshdate))+'-'+right('00'+cast(datepart(month, dateadd(quarter, -3, @refreshdate)) as varchar),2)
or [Month]=datename(year, dateadd(quarter, -4, @refreshdate))+'-'+right('00'+cast(datepart(month, dateadd(quarter, -4, @refreshdate)) as varchar),2)
);
FY
---------- --------------------
FY20/Q4 1
FY20/Q3 2
FY20/Q2 3
FY20/Q1 4
(4 rows affected)
So now it's quite simple to turn that into a cte (or temp table or whatever you prefer) and use that to join onto the base table
with RankedQuarters as (
select FY, row_number()over(order by [Month] desc) as RankQuarter
from dbo.YourTable
where
([Month]=datename(year, dateadd(quarter, -1, @refreshdate))+'-'+right('00'+cast(datepart(month, dateadd(quarter, -1, @refreshdate)) as varchar),2)
or [Month]=datename(year, dateadd(quarter, -2, @refreshdate))+'-'+right('00'+cast(datepart(month, dateadd(quarter, -2, @refreshdate)) as varchar),2)
or [Month]=datename(year, dateadd(quarter, -3, @refreshdate))+'-'+right('00'+cast(datepart(month, dateadd(quarter, -3, @refreshdate)) as varchar),2)
or [Month]=datename(year, dateadd(quarter, -4, @refreshdate))+'-'+right('00'+cast(datepart(month, dateadd(quarter, -4, @refreshdate)) as varchar),2)
)
)
select YourTable.Month, YourTable.quarter, YourTable.FY, RankedQuarters.RankQuarter
from dbo.YourTable
left join RankedQuarters on RankedQuarters.FY = YourTable.FY
Month quarter FY RankQuarter
---------- ------- ---------- --------------------
2018-12 Q1 FY19/Q1 NULL
2019-01 Q1 FY19/Q1 NULL
2019-02 Q2 FY19/Q2 NULL
2019-03 Q2 FY19/Q2 NULL
2019-04 Q2 FY19/Q2 NULL
2019-05 Q3 FY19/Q3 NULL
2019-06 Q3 FY19/Q3 NULL
2019-07 Q3 FY19/Q3 NULL
2019-08 Q4 FY19/Q4 NULL
2019-09 Q4 FY19/Q4 NULL
2019-10 Q4 FY19/Q4 NULL
2019-11 Q1 FY20/Q1 4
2019-12 Q1 FY20/Q1 4
2020-01 Q1 FY20/Q1 4
2020-02 Q2 FY20/Q2 3
2020-03 Q2 FY20/Q2 3
2020-04 Q2 FY20/Q2 3
2020-05 Q3 FY20/Q3 2
2020-06 Q3 FY20/Q3 2
2020-07 Q3 FY20/Q3 2
2020-08 Q4 FY20/Q4 1
2020-09 Q4 FY20/Q4 1
2020-10 Q4 FY20/Q4 1
2020-11 Q1 FY21/Q1 NULL
2020-12 Q1 FY21/Q1 NULL
2021-01 Q1 FY21/Q1 NULL
2021-02 Q2 FY21/Q2 NULL
2021-03 Q2 FY21/Q2 NULL
2021-04 Q2 FY21/Q2 NULL
2021-05 Q3 FY21/Q3 NULL
2021-06 Q3 FY21/Q3 NULL
2021-07 Q3 FY21/Q3 NULL
2021-08 Q4 FY21/Q4 NULL
2021-09 Q4 FY21/Q4 NULL
2021-10 Q4 FY21/Q4 NULL
2021-11 Q1 FY22/Q1 NULL
2021-12 Q1 FY22/Q1 NULL
2022-01 Q1 FY22/Q1 NULL
2022-02 Q2 FY22/Q2 NULL
2022-03 Q2 FY22/Q2 NULL
2022-04 Q2 FY22/Q2 NULL
2022-05 Q3 FY22/Q3 NULL
2022-06 Q3 FY22/Q3 NULL
2022-07 Q3 FY22/Q3 NULL
2022-08 Q4 FY22/Q4 NULL
2022-09 Q4 FY22/Q4 NULL
2022-10 Q4 FY22/Q4 NULL
2022-11 Q1 FY23/Q1 NULL
2022-12 Q1 FY23/Q1 NULL
2023-01 Q1 FY23/Q1 NULL
(50 rows affected)
To mock up what happens on another date, set @refreshdate to something else
set @refreshdate='1 may 2021'
Month quarter FY RankQuarter ---------- ------- ---------- -------------------- 2018-12 Q1 FY19/Q1 NULL 2019-01 Q1 FY19/Q1 NULL 2019-02 Q2 FY19/Q2 NULL 2019-03 Q2 FY19/Q2 NULL 2019-04 Q2 FY19/Q2 NULL 2019-05 Q3 FY19/Q3 NULL 2019-06 Q3 FY19/Q3 NULL 2019-07 Q3 FY19/Q3 NULL 2019-08 Q4 FY19/Q4 NULL 2019-09 Q4 FY19/Q4 NULL 2019-10 Q4 FY19/Q4 NULL 2019-11 Q1 FY20/Q1 NULL 2019-12 Q1 FY20/Q1 NULL 2020-01 Q1 FY20/Q1 NULL 2020-02 Q2 FY20/Q2 NULL 2020-03 Q2 FY20/Q2 NULL 2020-04 Q2 FY20/Q2 NULL 2020-05 Q3 FY20/Q3 4 2020-06 Q3 FY20/Q3 4 2020-07 Q3 FY20/Q3 4 2020-08 Q4 FY20/Q4 3 2020-09 Q4 FY20/Q4 3 2020-10 Q4 FY20/Q4 3 2020-11 Q1 FY21/Q1 2 2020-12 Q1 FY21/Q1 2 2021-01 Q1 FY21/Q1 2 2021-02 Q2 FY21/Q2 1 2021-03 Q2 FY21/Q2 1 2021-04 Q2 FY21/Q2 1 2021-05 Q3 FY21/Q3 NULL 2021-06 Q3 FY21/Q3 NULL 2021-07 Q3 FY21/Q3 NULL 2021-08 Q4 FY21/Q4 NULL 2021-09 Q4 FY21/Q4 NULL 2021-10 Q4 FY21/Q4 NULL 2021-11 Q1 FY22/Q1 NULL 2021-12 Q1 FY22/Q1 NULL 2022-01 Q1 FY22/Q1 NULL 2022-02 Q2 FY22/Q2 NULL 2022-03 Q2 FY22/Q2 NULL 2022-04 Q2 FY22/Q2 NULL 2022-05 Q3 FY22/Q3 NULL 2022-06 Q3 FY22/Q3 NULL 2022-07 Q3 FY22/Q3 NULL 2022-08 Q4 FY22/Q4 NULL 2022-09 Q4 FY22/Q4 NULL 2022-10 Q4 FY22/Q4 NULL 2022-11 Q1 FY23/Q1 NULL 2022-12 Q1 FY23/Q1 NULL 2023-01 Q1 FY23/Q1 NULL (50 rows affected)
