Quarter end date in Any date from last 12 month

  • What is best way to get quarter end date from 12 month for particular date

    I have not created table or anything this just simple query for expert

    Here is simple example this is my target date 07/31/2021 want will quarter for if i have 08/31/2021 it should come as 1 and end quarter date should be 11/30/2022.

    Period date       target date      quarter end date     quarter no

    08/31/2021      07/31/2022     11/30/2021             1

    09/30/2021     07/31/2022     11/30/2021              1

    10/31/2021      07/31/2022     11/30/2021              1

    11/30/2021     07/31/2022      02/28/2022            2

    12/31/2021     07/31/2022       02/28/2022           2

  • I suggest using a table to simplify the calcs:

    ;WITH test_data AS (
    SELECT CAST('08/31/2021' AS date) AS [Period date], CAST('07/31/2022' AS date) AS [target date],
    CAST('11/30/2021' AS date) AS [quarter end date], 1 AS [quarter no]
    UNION ALL
    SELECT '09/30/2021', '07/31/2022', '11/30/2021', 1
    UNION ALL
    SELECT '10/31/2021', '07/31/2022', '11/30/2021', 1
    UNION ALL
    SELECT '11/30/2021', '07/31/2022', '02/28/2022', 2
    UNION ALL
    SELECT '12/31/2021', '07/31/2022', '02/28/2022', 2
    ),
    month_to_period_table AS (
    SELECT * FROM ( VALUES(1, 2, 3), (2, 3, 1), (3, 3, 2), (4, 3, 2), (5, 4, 1), (6, 4, 2),
    (7, 4, 3), (8, 1, 1), (9, 1, 2), (10, 1, 3), (11, 2, 1), (12, 2, 2) )
    AS mtpt(month#, quarter#, month_in_quarter)
    )
    SELECT
    td.[Period date],
    DATEADD(MONTH, 4 - mtpt.month_in_quarter, td.[Period date]) AS [Calculated end quarter date],
    mtpt.quarter# AS [Calculated quarter no],
    td.[quarter end date] AS [Given end quarter date],
    td.[quarter no] AS [Given quarter no]
    FROM test_data td
    INNER JOIN month_to_period_table mtpt ON mtpt.month# = MONTH(td.[Period date])

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • Or maybe a query

    ;with test_data as (
    select cast('08/31/2021' as date) as [period date],
    cast('07/31/2022' as date) as [target date],
    cast('11/30/2021' as date) as [quarter end date],
    1 as [quarter no]
    union all
    select '09/30/2021', '07/31/2022', '11/30/2021', 1
    union all
    select '10/31/2021', '07/31/2022', '11/30/2021', 1
    union all
    select '11/30/2021', '07/31/2022', '02/28/2022', 2
    union all
    select '12/31/2021', '07/31/2022', '02/28/2022', 2)
    select [period date],
    calc.qtr_dt [period date quarter end],
    month(calc.qtr_dt)/4+1 quarter_num
    from test_data
    cross apply (values (cast(dateadd(qq, datediff(qq, 0, [period date])+1, -1) as date))) calc(qtr_dt);

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Thank you everyone works the way i want.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply