How to create your own financial year and group historical data accordingly-CodePudding

Can you help me , I have been trying to fetch data for past and current financial year ,

I have data from 2020 till date but i want the data to be grouped on financial year i.e April to March I have tried filtering data but can only filter it for a specific period like date>=April-21 <= March-22 and grouped by Year_month(date) but this will only show me data for that sepcific date period but how do i pull the whole data in the same format of a financial year

Select str_to_date(concat(date_format(DATE, '%Y-%m'), '-01'), '%Y-%m-%d') AS Month_Year, 
       Count(Id)
from ABC 
where DATE>="2020-01-01" 
and DATE<=Curdate()
group by str_to_date(concat(date_format(DATE, '%Y-%m'), '-01'), '%Y-%m-%d') AS Month_Year

What i got is this

Jan-20 dec-20  100
Jan-21 dec-21 500
Jan-22 Mar-22 300

Output Required is

Apr-20 to Mar-21  - 80
Apr-21 to Mar-22  - 700

CodePudding user response：

To group by a financial year that runs from the 1st April to 31st March you can use date_add() to subtract 3 months from the date before applying the function year().

 create table accounts(sales_amount int,trans_date date);
insert into accounts values(100,'2020-1-01');
insert into accounts values(100,'2020-2-01');
insert into accounts values(100,'2020-3-01');
insert into accounts values(100,'2020-4-01');
insert into accounts values(100,'2020-5-01');
insert into accounts values(100,'2020-6-01');
insert into accounts values(100,'2020-7-01');
insert into accounts values(100,'2020-8-01');
insert into accounts values(100,'2020-9-01');
insert into accounts values(100,'2020-10-01');
insert into accounts values(100,'2020-11-01');
insert into accounts values(100,'2020-12-01');
insert into accounts values(100,'2021-1-01');
insert into accounts values(100,'2021-2-01');
insert into accounts values(100,'2021-3-01');
insert into accounts values(100,'2021-4-01');
insert into accounts values(100,'2021-5-01');
insert into accounts values(100,'2021-6-01');
insert into accounts values(100,'2021-7-01');
insert into accounts values(100,'2021-8-01');
insert into accounts values(100,'2021-9-01');
insert into accounts values(100,'2021-10-01');
insert into accounts values(100,'2021-11-01');
insert into accounts values(100,'2021-12-01');

select 
  sum(sales_amount) turnover,
  concat('',
    year(date_add(trans_date, interval -3 month) ) ,
    '-',
    year(date_add(trans_date, interval  9 month) ) 
    ) financial_year
from accounts
group by  
  concat('',
    year(date_add(trans_date, interval -3 month) ) ,
    '-',
    year(date_add(trans_date, interval  9 month) ) 
    )  
order by  
  concat('',
    year(date_add(trans_date, interval -3 month) ) ,
    '-',
    year(date_add(trans_date, interval  9 month) ) 
    )

turnover | financial_year
-------: | :-------------
     300 | 2019-2020     
    1200 | 2020-2021     
     900 | 2021-2022

db<>fiddle here

CodePudding user response：

you can used the between in mysql. Correct me if I'm wrong, but In my persfective in the question you asked, you need to fetch the data from the date of Currect date (Month of March) to next month(April)?

If yes try this.

SELECT STR_TO_DATE(CONCAT(DATE_FORMAT(DATE, '%Y-%m'), '-01'), '%Y-%m-%d') AS Month_Year, COUNT(Id) FROM ABC WHERE DATE BETWEEN DATE((CURDATE() - INTERVAL 1 DAY) INTERVAL 1 MONTH) AND CURDATE() GROUP BY STR_TO_DATE(CONCAT(DATE_FORMAT(DATE, '%Y-%m'), '-01'), '%Y-%m-%d') AS Month_Year