CODEWITHSUNDEEP
sqlsql-servert-sql
Orin Moyer
Orin Moyer

Reputation: 509

Select Previous x Quarter dates

I need a SELECT to return the previous 12 quarters.

I can join in the data i need once i figure out how to get started. I have a snippet i use in another piece to return the previous 12 hours for a report. i am thinking it would be something similar..

;Declare @Date datetime =getdate()

;WITH DatesPre AS
(
    SELECT DATEPART(HOUR,DATEADD(HOUR,-1,@Date)) [Hour], 
      DATEADD(HOUR,-1,@Date+1) [Date], 1 Num
    UNION ALL
    SELECT DATEPART(HOUR,DATEADD(HOUR,-1,[Date])), 
      DATEADD(HOUR,-1,[Date]), Num+1
    FROM DatesPre
    WHERE Num < 12
)
SELECT [Hour]
FROM DatesPre

Here is sort of what i want to get to

enter image description here

Upvotes: 2

Views: 1278

Answers (2)

Orin Moyer
Orin Moyer

Reputation: 509

Thanks Gordon. for posterity sake - here was what i ended up with

WITH Q AS (
    SELECT datefromparts(year(getdate()), 1 + month(getdate()) / 3, 1) as [quarter], 1 as num
    UNION ALL
    SELECT dateadd(quarter, -1, [quarter]), num + 1
    FROM Q
    WHERE num < 12

),P as (
    Select '1/2' as pair
        UNION ALL 
    Select '3/4' as pair
        UNION ALL
    Select '7/8' as pair
        UNION ALL
    Select '10/11' as pair
        UNION ALL
    Select '13/14' as pair
)

SELECT [quarter],[pair]
FROM Q
  OUTER APPLY (
      SELECT * 
      FROM P
    ) pair

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

A recursive CTE is a viable approach:

WITH quarters AS (
      SELECT datefromparts(year(getdate()), 1 + month(getdate()) / 3, 1) as q, 1 as num
      UNION ALL
      SELECT dateadd(quarter, -1, q), num + 1
      FROM quarters
      WHERE num < 12
     )
SELECT q
FROM quarters;

Here is a db<>fiddle.

Upvotes: 2

Related Questions