Showing what quarter of a financial year a date is in

Question

I'm trying to construct a query that will map two columns, one, a date from a table, the second column an alias to show what quarter and financial year the date falls into.

Unfortunately I don't have enough knowledge of SQL to know where to begin. I know that I'd do this with a combination of getdate() and dateadd(MONTH,,) however nothing that I've put together has come close to working.

To further complicate this the financial years in Australia go from 1st July- 30th of June so quarter one of financial year 2012 would start from July 1st 2012.

I can do this without a statement however I'd much rather have it in a statement as it's going to be used in an SSRS report and a C# application and it would make maintenance significantly easier.

Answer 1

This should work:-

SELECT
    MyDate,
    CASE
        WHEN MONTH(MyDate) BETWEEN 1  AND 3  THEN convert(char(4), YEAR(MyDate) - 1) + 'Q3'
        WHEN MONTH(MyDate) BETWEEN 4  AND 6  THEN convert(char(4), YEAR(MyDate) - 1) + 'Q4'
        WHEN MONTH(MyDate) BETWEEN 7  AND 9  THEN convert(char(4), YEAR(MyDate) - 0) + 'Q1'
        WHEN MONTH(MyDate) BETWEEN 10 AND 12 THEN convert(char(4), YEAR(MyDate) - 0) + 'Q2'
    END AS Quarter
FROM
    MyTable

Output:-

MyDate        Quarter
----------    --------
2011-01-01    "2010Q3"
2011-04-01    "2010Q4"
2011-07-01    "2011Q1"
2011-10-01    "2011Q2"

Answer 2

This method doesn't require a CASE statement, and works for any monthly fiscal calendar.

DECLARE @firstMonthOfFiscalQ1 int = 7; --1=January
SELECT DateColumn
    , FLOOR(((12 + MONTH(DateColumn) - @firstMonthOfFiscalQ1) % 12) / 3 ) + 1 AS FiscalQuarter
    FROM SomeTable 
    WHERE 1=1;