Grouping financial data based on Financial year Quarters by using the date column
We can get quarter by combining three months in one group. By using QUARTER() function we can get number in 1 to 4 based on the month.
Jan to March will return 1, we will call it Quarter 1st of calendar year.
April to June will return 2, we will call it Quarter 2nd .
July to Sep will return 3, we will call it Quarter 3rd .
Oct to Dec will return 4 , we will call it Quarter 4th
SELECT QUARTER(‘2018-03-12’)
Above query will return 1.
We can get the Quarter of todays date by using CURDATE().
First let us check the calendar year data based on Quarters. We can group the number of records based on Quarters
SELECT QUARTER(payment_dt) , COUNT(*) from plus2_bills
GROUP BY QUARTER(payment_dt)
By adding a WHERE condition we can get records based on quarters of any particular year.
SELECT QUARTER(payment_dt) , count(*) from plus2_bills
WHERE YEAR(payment_dt)='2016' GROUP BY QUARTER(payment_dt)
Each quarter of different years
SELECT CONCAT(YEAR(payment_dt), '-Q',QUARTER(payment_dt)) as qt_year ,COUNT(*) as Nos,SUM(amount) as total,AVG(amount) as average FROM `plus2_bills` GROUP BY qt_year
Financial Year and grouping by Quarters
In a financial year Quarters are different than calendar year Quarters. In some case financial year starts from 1st April and ends on 31st March of next year.
• 1st or Q1 is from 1st April to 30th of June.
• 2nd Quarter is from July 1st to Sep 30th.
• 3rd Quarter is from Oct 1st to Dec 31st.
• 4th Quarter is from 1st Jan to 31st March.
We can assign each record to a Financial year quarter by using the date column.
SELECT DATE_FORMAT( payment_dt, '%Y-%M-%d' ) AS DATE,CASE WHEN QUARTER(payment_dt)=1
THEN CONCAT(YEAR(payment_dt)-1, '-Q',QUARTER(payment_dt)+3)
ELSE concat(YEAR(payment_dt),'-Q',QUARTER(payment_dt)-1)
END AS qt_year
FROM `plus2_bills`
We can change the above query to Financial year starting from Oct to Next year September.
SELECT DATE_FORMAT( payment_dt, '%Y-%M-%d' ) AS DATE,
CASE WHEN QUARTER( payment_dt ) !=4
THEN CONCAT( YEAR( payment_dt ) -1,'-Q', QUARTER( payment_dt ) +1 )
ELSE CONCAT( YEAR( payment_dt ) , '-Q', QUARTER( payment_dt )-3 )
END AS qt_year
FROM `plus2_bills`
Grouping data along Financial year and Quarter
SELECT CASE WHEN QUARTER(payment_dt)=1
THEN CONCAT(YEAR(payment_dt)-1, '-Q',QUARTER(payment_dt)+3)
ELSE concat(YEAR(payment_dt),'-Q',QUARTER(payment_dt)-1)
END AS qt_year ,
COUNT(*) as Nos,SUM(amount) as total,AVG(amount) as average
FROM `plus2_bills` GROUP BY qt_year
If we consider October to September as Financial year
SELECT
CASE WHEN QUARTER( payment_dt ) !=4
THEN CONCAT( YEAR( payment_dt ) -1,'-Q', QUARTER( payment_dt ) +1 )
ELSE CONCAT( YEAR( payment_dt ) , '-Q', QUARTER( payment_dt )-3 )
END AS qt_year,
COUNT(*) as Nos,SUM(amount) as total,AVG(amount) as average
FROM `plus2_bills` GROUP BY qt_year
Financial year staring from July to next year June
SELECT
CASE WHEN QUARTER( payment_dt ) less than =2
THEN CONCAT( YEAR( payment_dt ) -1,'-Q', QUARTER( payment_dt ) +2 )
ELSE CONCAT( YEAR( payment_dt ) , '-Q', QUARTER( payment_dt )-2 )
END AS qt_year,
COUNT(*) as Nos,SUM(amount) as total,AVG(amount) as average
FROM `plus2_bills` GROUP BY qt_year
Format in YYYY-YY-Q
SELECT
CASE WHEN QUARTER(payment_dt)=1
THEN CONCAT(YEAR(payment_dt)-1, '-',DATE_FORMAT(payment_dt,'%y'), '-Q',QUARTER(payment_dt)+3)
ELSE concat(YEAR(payment_dt), '-',DATE_FORMAT(payment_dt,'%y')+1,'-Q',QUARTER(payment_dt)-1)
END AS qt_year ,
COUNT(*) as Nos,SUM(amount) as total,AVG(amount) as average
FROM `plus2_bills` GROUP BY qt_year