Custom column to show dates/Quarter label - Year

[Kaushik2020]

Dear All, 

I have a scenario where i get dates in CreatedDate column in my database in below format. I want to show a column named CustomDate which show the last/max date for the end month. below are the conditions for Quarters.

Q1 -> 01 Nov Previous year to Current year Feb end. refer below example dates
01-Nov-2024
27-Nov-2024
10-Dec-2024
10-Feb-2025

For these dates i want a column 

Q2 -> 01 March Current year till 31 May Current year
Q3 -> 01 June Current year till 31 Aug Current year
Q4 -> 01 Sept current year till 31 Oct Current year

Based on the example above, I would like to see dates in CustomDates column as below. 

28-Feb-2025

31-May-2025

31-Aug-2025

31-Oct-2025

Thanks in advance.

[Or]

You can use the Quartername() function to do this easily, but you'll have to add pick/match or an if() statement to control the exact formatting.

Quartername(Date,0,12) should be the right syntax, I believe, though it might be (Date,-1,12), not 100% sure and can't test right now. 

[Kaushik2020]

I will wait for more suggestions in this. with SQL query it is working well. but it is taking too much time in query load. Here the issue is these months are not part of generic quarters hence we need to tag them based on the months. Your prompt revert to the post is much appreciated. 

[Nagaraju_KCS · Accepted solution]

Try this

SELECT
CreatedDate,
CASE
WHEN MONTH(CreatedDate) IN (11, 12)
THEN EOMONTH(DATEFROMPARTS(YEAR(CreatedDate) + 1, 2, 1))

WHEN MONTH(CreatedDate) IN (1, 2)
THEN EOMONTH(DATEFROMPARTS(YEAR(CreatedDate), 2, 1))

WHEN MONTH(CreatedDate) IN (3, 4, 5)
THEN EOMONTH(DATEFROMPARTS(YEAR(CreatedDate), 5, 1))

WHEN MONTH(CreatedDate) IN (6, 7,8)
THEN EOMONTH(DATEFROMPARTS(YEAR(CreatedDate), 8, 1))

WHEN MONTH(CreatedDate) IN (9, 10)
THEN EOMONTH(DATEFROMPARTS(YEAR(CreatedDate), 10, 1))

ELSE NULL
END AS CustomDate
FROM
YourTableName;

[Kaushik2020]

Thanks for the reply. I have currently handled this via the process you mentioned via ETL.