Show All months related to Quarter

[bhargavikn]

Hello All,

I'm trying to show all related months of Quarter in QuarterMonth column.

Example:

Year	Quarter	Month	QuarterMonth
2022	Q1	Dec-22	Q1 (Dec-22,Jan-23,Feb-23)
2023	Q1	Jan-23	Q1 (Dec-22,Jan-23,Feb-23)
2023	Q1	Feb-23	Q1 (Dec-22,Jan-23,Feb-23)

Could you please help me with the solution.

Thank you.

[Lakshmanan]

Hi, 

Do a left join on Quarter 

LEFT JOIN (Input_Data)
Load * inline [
Month_ID, MonthName, Quarter

1,Jan,Q1

2,Feb,Q1

3,Mar,Q1

4,Apr,Q2

5,May,Q2

6,Jun,Q2

7,Jul,Q3

8,Aug,Q3

9,Sep,Q3

10,Oct,Q4

11,Nov,Q4

12,Dec,Q4

];

[FinalTable]:
Load Quarter,Year,Month,CONCAT(MonthName& '-'& Year,',') as Quarter_Months
Resident [Input_Data]
group by Quarter,Year,Month
order by Month_ID;
drop table Input_Data;

You can Trim the year using Left(Year,2)

Please mark as Closed if it resolved the issue.

[bhargavikn]

Hi Lakshmanan,

Thank you for your reply. I have followed same steps but I'm getting wrong output

Code:

Output:

Can you please guide me.

And also, in your screenshot for May-23, it is showing months for 2022 year.  Please check.

Thank you.

[Lakshmanan]

Hi bhargavikn, 

I have added year as 2022 ,month as May-23 , thats the reason it shows different . Can you share you input data to investigate further .

[bhargavikn]

Hi,

you can use input data as below 

Year	Month	Quarter
2022	Jun-22	Q3
2022	Jul-22	Q3
2022	Aug-22	Q3
2022	Sep-22	Q4
2022	Oct-22	Q4
2022	Nov-22	Q4
2022	Dec-22	Q1
2023	Jan-23	Q1
2023	Feb-23	Q1
2023	Mar-23	Q2
2023	Apr-23	Q2
2023	May-23	Q2
2023	Jun-23	Q3
2023	Jul-23	Q3
2023	Aug-23	Q3

Required Output:

Quarter	QuarterMonth	Month
Q1	Q1 (Dec-22,Jan-23,Feb-23)	Dec-22
Q1	Q1 (Dec-22,Jan-23,Feb-23)	Jan-23
Q1	Q1 (Dec-22,Jan-23,Feb-23)	Feb-23

[Lakshmanan]

[Input_Data]:
Load * inline [
Year1, Quarter, Month
2022, Q1, Dec-22
2023, Q1, Jan-23
2023, Q1, Feb-23
2023, Q2, Mar-23
2023, Q2, Apr-23
2023, Q3, May-23
];

Left Join (Input_Data)
Load Quarter, Quarter &'('&Concat(Month,',') &')' as MonthName
Resident [Input_Data]
Group by Quarter;

Please mark as closed ,if it resolved the issue.