Specify Quarter dates

[#1]

Hi everyone

This is my first post as am a little stuck on a date issue.

I have a scenario where there are two types of clients and quarterly valutions are done as per dates below

Days per quarter:

Quarterly:

Q1 – Ending 31/03/yyyy – 90

Q2 – Ending 30/06/yyyy – 91

Q3 – Ending 30/09/yyyy – 92

Q4 – Ending 31/12/yyyy - 92

Quartertax

Q1 – Ending 05/04/yyyy – 95

Q2 – Ending 30/06/yyyy – 86

Q3 – Ending 30/09/yyyy – 92

Q4 – Ending 31/12/yyyy - 92

I am unable to figure out a function whereby I can specify the dates in each quarter for past years and futureproofing so that I can assign the correct quarter to the dates within that quarter.

Any help would be greatly appreciated.

Many Thanks

Sye

[#2]

I would recommend creating a date table that includes quarters. Pre-creating your date data will have all kinds of benefits not the least of which include simplified development and better performance. Here's a snippet of code that I would use to pre-prepare quarters:

select

//include all your other date attributes like month, week, day of year, year, etc...

[QuarterID] = convert(INT,convert(varchar(4),datepart(YYYY,getdate()))+convert(varchar(2),datepart(QQ,getdate()))) ,

[Quarter_YYYY-QQ] = convert(varchar(4),datepart(YYYY,getdate()))+'-'+'Q'+convert(varchar(2),datepart(QQ,getdate())) ,

[Quarter_YYYY-Q] = convert(varchar(4),datepart(YYYY,getdate()))+'-'+convert(varchar(2),datepart(QQ,getdate())) ,

[Quarter_QQ-YYYY] = 'Q'+convert(varchar(2),datepart(QQ,getdate()))+'-'+convert(varchar(4),datepart(YYYY,getdate())),

[Quarter_Q-YYYY] = convert(varchar(2),datepart(QQ,getdate()))+'-'+convert(varchar(4),datepart(YYYY,getdate())) ,

[Quarter_QQ] = 'Q'+convert(varchar(2),datepart(QQ,getdate())) ,

[Quarter_Q] = convert(int,datepart(QQ,getdate()))

Instead of getDate() you would want to create a script that would loop through days from "startdate" to "finishdate" to pre-build a calendar. However, the above code would work for current day as-is.

On a related note, you may also want to add offsets (current Q is 0, last Q is -1, prior is -2, etc) to your date table so you can easily do QTD and Current vs Previous evaluations.