Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.