CODEWITHSUNDEEP
mysqlsql
user10705797
user10705797

Reputation:

How to get financial year data using My Sql

I have a query which is giving me the result on the basis of current year, but it's taking year from January to December : I want it to be from 1st of april to 31 of march of next year

How can I achieve that?

My query :

select 
  distinct round(sum(case when year(curdate())=year(billdate) and month(curdate())=month(billdate) then netamount else 0 end)) as MtdAmount,
  round(sum(case when year(curdate())=year(billdate) then netamount else 0 end)) as YtdAmount 
from syncbill where cancelled<>'Y'

Upvotes: 0

Views: 81

Answers (2)

Nick
Nick

Reputation: 147266

You need to adjust both billdate and CURDATE() forwards 9 months to compare their financial years (FY19 being from 2018-04-01 to 2019-03-31). Note we can simplify the MtdAmount compare using DATE_FORMAT. This query should work: 

SELECT
    ROUND(SUM(CASE WHEN DATE_FORMAT(CURDATE(), '%Y%m') = DATE_FORMAT(billdate, '%Y%m') THEN netamount ELSE 0 END)) AS MtdAmount,
    ROUND(SUM(CASE WHEN YEAR(CURDATE() + INTERVAL 9 MONTH) = YEAR(billdate + INTERVAL 9 MONTH) THEN netamount ELSE 0 END)) AS YtdAmount 
FROM syncbill
WHERE cancelled<>'Y'

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271141

Subtract three months and use the calendar year. I think you want something like this:

select round(sum(case when year(billdate - interval 3 month) = year(curdate()) and
                           month(curdate()) = month(billdate)
                      then netamount
                      else 0 
                 end)
            ) as MtdAmount,
       round(sum(case when year(curdate()) = year(billdate - interval 3 month)
                      then netamount
                      else 0
                 end)
            ) as YtdAmount 
from syncbill
where cancelled <> 'Y';

I don't think the distinct is doing anything. But if it is, you can add it back in or add the appropriate group by keys.

Upvotes: 0

Related Questions