May 13, 2022 at 12:53 am
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
May 13, 2022 at 1:29 am
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) A socialist is someone who will give you the shirt off *someone else's* back.
May 13, 2022 at 1:44 pm
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
May 15, 2022 at 7:41 pm
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