1

I am having some real trouble trying to figure out a nice way to get some SQL in my access database to create a Financial Quarter result based on a date in the format dd/mm/yyyy (UK). I have a Column called 'Date' in the table 'DimDate' and I have a column called 'FinancialQuarter' within the same table. So I essentially want the SQL to work out the following:

If the month is between 1 and 3 equals Q1, If the month is between 4 and 6 equals Q2, If the month is between 7 and 9 equals Q3, If the month is between 10 and 12 equals Q4,

I have some basic SQL skills but I am no expert. I found a post in StackOverflow similar to this question however I made no progress from it. The SQL code they used which I modified was as follows:

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

My code does not work at all, and its probably completely wrong, any help would be highly appreciated!

Thanks James.

Matt
  • 14,906
  • 27
  • 99
  • 149
James Whitelegg
  • 79
  • 3
  • 8
  • 1
    Access SQL does not support the `CASE WHEN` construct. The corresponding mechanism in Access SQL is the [Switch()](https://support.office.com/en-us/article/Switch-Function-d750c10d-0c8e-444c-9e63-f47504f9e379) function. – Gord Thompson Mar 09 '15 at 11:01

1 Answers1

1

Realised your using access

DatePart('q',[date])

DATEPART QUARTER

SELECT date, DATEPART(QUARTER, date) as FinancialQuarter 
FROM DimDate
Matt
  • 14,906
  • 27
  • 99
  • 149