Forum Discussion

srini040711's avatar
srini040711
Copper Contributor
Jan 31, 2024
Solved

Need Help with SQL Query(first day and last day of each quarter in every year)

Hi Everyone, 

 

Could someone help me with SQL query to get the result like below(first day and last day of each quarter in every year)

 

Thanks in Advance.

YearYear-QuarterQuarter StartQuarter End
20212021 Q11/1/20213/31/2021
20212021 Q24/1/20216/30/2021
20212021 Q37/1/20219/31/2021
20212021 Q410/1/202112/31/2021
20222022 Q11/1/20223/31/2022
20222022 Q24/1/20226/30/2022
20222022 Q37/1/20229/31/2022
20222022 Q410/1/202212/31/2022
20232023 Q11/1/20233/31/2023
20232023 Q24/1/20236/30/2023
20232023 Q37/1/20239/31/2023
20232023 Q410/1/202312/31/2023
20242024 Q11/1/20243/31/2024
20242023 Q24/1/20246/30/2024
20242023 Q37/1/20249/31/2024
20242023 Q410/1/202412/31/2024
  • Hi hope you are doing well, see the script below

    DECLARE @StartDate DATE = '2021-01-01';
    DECLARE @EndDate DATE = '2024-12-31';

    WITH DateSequence AS (
    SELECT @StartDate AS Date
    UNION ALL
    SELECT DATEADD(DAY, 1, Date)
    FROM DateSequence
    WHERE Date < @EndDate
    )
    SELECT
    CONCAT(YEAR(Date),' Q',DATEPART(QUARTER, Date)) AS [Year - Quarter]
    ,MIN(Date) AS [Quarter Start]
    ,MAX(Date) AS [Quarter End]
    FROM DateSequence
    GROUP BY CONCAT(YEAR(Date),' Q',DATEPART(QUARTER, Date))
    ORDER BY MIN(Date)
    OPTION (MAXRECURSION 0);

    It uses recursive query as described here: https://janzednicek.cz/en/sql-loop-do-you-know-how-to-use-while-or-recursive-cte/. Maybe I would recommend to create a calendar (also in the article) in case you need to access information like this repeatedly.

2 Replies

  • JanZednicek's avatar
    JanZednicek
    Copper Contributor

    Hi hope you are doing well, see the script below

    DECLARE @StartDate DATE = '2021-01-01';
    DECLARE @EndDate DATE = '2024-12-31';

    WITH DateSequence AS (
    SELECT @StartDate AS Date
    UNION ALL
    SELECT DATEADD(DAY, 1, Date)
    FROM DateSequence
    WHERE Date < @EndDate
    )
    SELECT
    CONCAT(YEAR(Date),' Q',DATEPART(QUARTER, Date)) AS [Year - Quarter]
    ,MIN(Date) AS [Quarter Start]
    ,MAX(Date) AS [Quarter End]
    FROM DateSequence
    GROUP BY CONCAT(YEAR(Date),' Q',DATEPART(QUARTER, Date))
    ORDER BY MIN(Date)
    OPTION (MAXRECURSION 0);

    It uses recursive query as described here: https://janzednicek.cz/en/sql-loop-do-you-know-how-to-use-while-or-recursive-cte/. Maybe I would recommend to create a calendar (also in the article) in case you need to access information like this repeatedly.

    • srini040711's avatar
      srini040711
      Copper Contributor
      Hi JanZednicek, really thank you for your response and time and efforts

Resources