How to increase the values in the select statement dynamically each month-CodePudding

I need to break date span into quarter of the year. I'm using the below query in main query but as the maximum value reached in the cross apply statement I'm not getting the latest quarter's from the select statement. How to increase the number dynamically in the Values of the select statement. I suppose to get 2022Q2, 2022Q3 and currently its 2022Q4 but the query stopped at 2022Q1 because the Values in the select statement is 12 maximum.

Select CAST(YEAR(DATEADD(QQ,n,'2019-01-01')) AS VARCHAR(4))   'Q'   DATENAME(qq,DATEADD(QQ,n,'3000-12-31')) as Year_Quarter
from(
SELECT TOP(1 DATEDIFF(QQ,'2019-01-01','2999-12-31')) n
FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) rc(n)) as x
order by 1

I tried adding the numbers to the Values then getting

Select CAST(YEAR(DATEADD(QQ,n,'2019-01-01')) AS VARCHAR(4))   'Q'   DATENAME(qq,DATEADD(QQ,n,'3000-12-31')) as Year_Quarter
from(
SELECT TOP(1 DATEDIFF(QQ,'2019-01-01','2999-12-31')) n
FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15)) rc(n)) as x
order by 1

I want the Values in the inner select to add dynamically to get the current quarter.

CodePudding user response：

As I mentioned, use a Tally. An inline one would look like this:

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT 0 AS I
    UNION ALL
    SELECT TOP (DATEDIFF(QUARTER,'20190101','29991231'))
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
    FROM N N1, N N2, N N3, N N4, N N5, N N6, N N7, N N8)
SELECT DATEADD(QUARTER,T.I, '20190101'),
       CONCAT(YEAR(DATEADD(QUARTER,T.I, '20190101')),'Q',DATEPART(QUARTER,DATEADD(QUARTER,T.I, '20190101')))
FROM Tally T;

If you wanted to create a function, then you could create it and use it something like this:

CREATE OR ALTER FUNCTION [fn].[Tally] (@End bigint, @StartAtOne bit) 
RETURNS table
AS RETURN
    WITH N AS(
        SELECT N
        FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
    Tally AS(
        SELECT 0 AS I
        WHERE @StartAtOne = 0
        UNION ALL
        SELECT TOP (@End)
               ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
        FROM N N1, N N2, N N3, N N4, N N5, N N6, N N7, N N8)
    SELECT I
    FROM Tally;
GO

SELECT DATEADD(QUARTER,T.I, '20190101'),
       CONCAT(YEAR(DATEADD(QUARTER,T.I, '20190101')),'Q',DATEPART(QUARTER,DATEADD(QUARTER,T.I, '20190101')))
FROM fn.Tally(DATEDIFF(QUARTER,'20190101','29991231'),0) T;

CodePudding user response：

You can cross join two value tables

This always displays the most recent 16 quarters

SELECT CONVERT(varchar(4), (YEAR(GETDATE()) - j.n))   'Q'   CONVERT(varchar(1), q.n) as quarter
FROM
 (VALUES (0), (1), (2), (3), (4)) AS j(n)
 CROSS JOIN
 (VALUES (1), (2), (3), (4)) AS q(n)
WHERE
    (j.n > 0 OR q.n <= DATEPART(q, GETDATE())) AND
    (j.n < 4 OR q.n >  DATEPART(q, GETDATE()))
ORDER BY j.n DESC, q.n ASC

Here I use j(n) as year offset that I subtract from the current year to get the last 4 years up the the current one.

The calculated year is then converted to a varchar and concatenated with 'Q' and the quarter. Note that the year is not hard-coded, so that the query dynamically returns all the quarters from the last four years.

Output created on 2023-01-01:

CodePudding user response：

I think what you want it to go from a fixed start date to a dynamic end date and no further. This code works by getting a hardcoded @StartDate, works out the end of the last quarter to have finished, it then uses a recursive CTE to iterate over dates adding 1 quarter every time, and some string manipulation to get the formatting correct. Once GETDATE() moves into a new quarter the CTE will return a 17th value without having to change the code.

DECLARE @StartDate DATETIME = '2019-01-01';
DECLARE @LastQuarterEndDate DATETIME = DATEADD(D, -1, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), 0));

WITH recCTE
AS (
    SELECT ConCat(DATEPART(YYYY, @StartDate), 'Q', DATEPART(QQ, @StartDate)) AS DateQuarter, @StartDate AS Date
    UNION ALL
    SELECT CONCAT(DATEPART(YYYY, DATEADD(QQ, 1, recCTE.Date)), 'Q', DATEPART(QQ, DATEADD(QQ, 1, recCTE.Date))),
           DATEADD(QQ, 1, recCTE.Date)
    FROM recCTE
    WHERE recCTE.Date < @LastQuarterEndDate
)
SELECT * FROM recCTE;

CodePudding user response：

Yet another way is to build a sequence by replicating the number of quarters and then splitting, calculating the year and quarter number based on the row_number:

DECLARE @d date = GETDATE();

DECLARE @q int  = DATEPART(QUARTER, @d),
        @s date = '20190101';

SELECT d, q = CONCAT_WS('Q', YEAR(d), r IIF(r=0,4,0)) 
FROM (SELECT DATEADD(QUARTER, r-1, @s), r%4
 FROM (SELECT ROW_NUMBER() OVER (ORDER BY @@SPID)
  FROM STRING_SPLIT(REPLICATE(',', 
   (4*(YEAR(@d) - YEAR(@s))) @q-1),',')
  ) y(r)) z(d,r);

Output:

d	q
2019-01-01	2019Q1
2019-04-01	2019Q2
2019-07-01	2019Q3
2019-10-01	2019Q4
2020-01-01	2020Q1
2020-04-01	2020Q2
2020-07-01	2020Q3
2020-10-01	2020Q4
2021-01-01	2021Q1
2021-04-01	2021Q2
2021-07-01	2021Q3
2021-10-01	2021Q4
2022-01-01	2022Q1
2022-04-01	2022Q2
2022-07-01	2022Q3
2022-10-01	2022Q4

Working example in this fiddle.