SQL fiscal quarter totals... I've already got the calendar ones

Here's the SQL query I have for the calendar year's quarterly totals

SELECT  
SUM(CASE WHEN WEEK(LAST_DAY) <= 13 THEN BILLABLE END) AS Q1 
,SUM(CASE WHEN WEEK(LAST_DAY) >= 14 AND WEEK(LAST_DAY) <= 26 THEN BILLABLE END) AS Q2 
,SUM(CASE WHEN WEEK(LAST_DAY) >= 27 AND WEEK(LAST_DAY) <= 39 THEN BILLABLE END) AS Q3
,SUM(CASE WHEN WEEK(LAST_DAY) >= 40 AND WEEK(LAST_DAY) <= 53 THEN BILLABLE END) AS Q4,
Emp_Name
FROM 'emp_info' 
WHERE YEAR(LAST_DAY) = YEAR(CURRENT_TIMESTAMP)
GROUP BY Emp_Name

I need to shift it so it gives me the fiscal year (July 1 to June 30) totals.

I know when do开发者_运维技巧ing so the week numbers will need to start on July 1, not January 1 and there'll probably be a case statement in there somewhere. But I can't get it to come out correctly.

If I were you, I'd store fiscal quarters in a table. Then your queries would be dead simple. And not only would they be dead simple, it would be obvious when they were correct.

Try this query -

SELECT
  Emp_Name,
  IF(DATE(LAST_DAY) <= DATE('2011-6-30'), YEAR(LAST_DAY) - 1, YEAR(LAST_DAY)) f_year,
  QUARTER(LAST_DAY) quarter,
  SUM(BILLABLE)
FROM
  emp_info
GROUP BY
  Emp_Name,
  f_year,
  quarter;

You are aware that your query doesn't divide year into 4 quarters by month? Instead of using the WEEK() function use MONTH().

By operating on months and years (YEAR()) calculating sum for fiscal year will be easy.

Your real problem is going to be that generally speaking, date and time functions will only work on ISO/(Other common) calendar date/time objects, but you need them to work on a company-specific fiscal calendar.

This is why Calendar files are used, as a translation between fiscal and ISO calendars. Generate your calendar fileout for a few years in either direction, then you can join to it, restricting/grouping by fiscal year and period. No case statements needed.

I also recommend storing most dates in your tables as actual ISO dates, then only translating/using fiscal dates when actually necessary - we have some summation tables here which are keyed off fiscal year/period, but which display shopping-behaviour data to customers (who don't know/care when our fiscal year is)...