dimanche 1 février 2015

Build Calendar With Quarters Included

I know how to build a SQL Calendar with month & Year. But I need to also add Quarters into the calendar which I'm not sure how to set-up. For example, sample data output would need to be Q1 January February March Q2 April May June Q3 July August September Q4 October November December


What is the best syntax for creating a calendar with quarter information, or how could my syntax be tweaked to add in the quarter information as well? This is what I am currently using



Declare @startdate DateTime, @enddate DateTime;
Select @startdate = '2014-01-01', @enddate = '2016-12-31'
;With Num1 (n) As (
Select 1 as n
Union All Select n+1 as n
From Num1 Where n<101),
Num2 (n) As (Select 1 From Num1 As X, Num1 As Y),
Nums (n) As (Select Row_Number() Over(Order By n) From Num2)

,myDateCTE As
(Select Cast(dateadd(day,n-1,@startdate) as date) dt
from Nums)

Select dt, datename(month, dt) from myDateCTE
Where dt <= @enddate

Aucun commentaire:

Enregistrer un commentaire