fiscal year..

[techker]

Hey guys im dealing with a fiscal year issue..

 

so the script i did is for my body's school..so they have fiscal years..2011-2012 between march and April..

 

so the databse is set already with a date field.

 

date_field

 

2012-01-10 15:53:41

 

is there a way to make it easy and only show fiscal year?cause this year is ok but next year i don't need to show the previous years..

[joel24]

SELECT
   CASE WHEN MONTH(date_field)>=3 THEN
          concat(YEAR(date_field), '-',YEAR(date_field)+1)
   ELSE concat(YEAR(date_field)-1,'-', YEAR(date_field)) END AS financial_year,
   SUM(amount_counting_field)
FROM theTable
GROUP BY financial_year

//change the amount_counting_field to the field you want to aggregate for the years and the table name.

 

sourced from stack overflow()

 

#edit#

if you want to select the rows in that year and not an aggregated value;

SELECT *
FROM theTable
WHERE 
(month(date_field) >= 3 AND year(date_field) = 2012) 
OR
(month(date_field) < 3 AND year(date_field) = 2013) 

[techker]

ok..wow.lol

 

whats the = 3

 

if i do a settings in the admin panel that he selects what fiscal hear to show?

 

like 2012-2013

 

then in the search i could but the function?

 

like where date = $fiscal_year..

 

 

[joel24]

3 is the 3rd month... march...

if you want greater than march (i.e. april 2012 to march 2013, then set it to > rather than >=)

 

not sure what you mean...

if i do a settings in the admin panel that he selects what fiscal hear to show?

 

like 2012-2013

 

then in the search i could but the function?

 

like where date = $fiscal_year..