Take the 2-minute tour ×

Ok, I need some help with this. I think I may have been looking at this too long and its messing with my brain. I have a table, we'll call it Goals. In this table I have the following 4 columns:

StaffID example (123) Syear example (2011) Smonth example (7) SGoal example (104.2)

What I need is to sum the SGoal column for Fiscal Year, ie months 7-12 of one year and 1-6 of the next. Problem is I need this to be looping and by that I mean it cant be year specific. I will be using this query to run a report in BIZ for years to come. So I think it may be able to be done 2 ways, either through a query or modifying the table somehow. Either way, any help would be greatly appreciated.

share|improve this question
What exactly do you need help with? You want ot make two new columns. –  Chase Ernst Jul 12 '13 at 16:29

3 Answers 3

It's probably best to put the fiscal year calculation into a View but here's one way to do it in a query

SELECT staffid, syear+(((smonth+1)&8)/8) as fiscalyear, sum(sgoal) AS 'sum' 
FROM goals 
GROUP BY staffid, syear+(((smonth+1)&8)/8) 
share|improve this answer
FROM Goals
WHERE (Syear = 2011 AND Smonth BETWEEN 7 AND 12) OR 
      (Syear = 2012 AND Smonth BETWEEN 1 AND 6)
share|improve this answer
Can you expand on why/how this resolves the question being asked? –  Denomales Jul 15 '13 at 20:36

Something like this would work, although storing the fiscal year would be a better and more elegant solution.

select staffId, fiscal_year, sum(sGoal) from (
select staffid, sGoal, if (sMonth > 6, sYear, sYear-1) as fiscal_year
  from goals ) as fiscal
group by staffId, fiscal_year

SQL Fiddle

share|improve this answer

Your Answer


