Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table randomdates( date_ )
as
select sysdate - dbms_random.value( 1, 600 )
from dual 
connect by level <= 50 ;
50 rows affected

select date_
, to_char( date_, 'YYYY-Q' ) quarter
from randomdates 
;


DATE_
QUARTER


10-APR-20
2020-2

16-AUG-21
2021-3

24-JUL-20
2020-3

14-APR-20
2020-2

13-OCT-20
2020-4

29-NOV-20
2020-4

10-FEB-21
2021-1

09-NOV-20
2020-4

27-APR-21
2021-2

29-SEP-20
2020-3

08-JAN-21
2021-1

02-AUG-20
2020-3

30-JUN-20
2020-2

29-AUG-21
2021-3

23-JUN-21
2021-2

07-JUL-20
2020-3

24-MAY-20
2020-2

12-SEP-21
2021-3

03-MAY-20
2020-2

07-APR-21
2021-2

13-OCT-20
2020-4

08-AUG-21
2021-3

30-APR-21
2021-2

26-OCT-21
2021-4

20-NOV-20
2020-4

24-AUG-20
2020-3

05-MAR-21
2021-1

01-FEB-21
2021-1

06-DEC-20
2020-4

05-AUG-21
2021-3

13-FEB-21
2021-1

26-DEC-20
2020-4

26-JUN-20
2020-2

18-JUL-21
2021-3

18-JUL-21
2021-3

30-AUG-21
2021-3

08-JUL-21
2021-3

12-JAN-21
2021-1

03-MAY-21
2021-2

28-MAY-20
2020-2

01-JAN-21
2021-1

06-AUG-21
2021-3

08-MAY-20
2020-2

06-NOV-21
2021-4

20-APR-21
2021-2

29-MAR-20
2020-1

05-SEP-20
2020-3

26-AUG-21
2021-3

16-APR-21
2021-2

19-JUN-21
2021-2


select to_char( date_, 'YYYY-Q' ), count(*)
from randomdates 
group by to_char( date_, 'YYYY-Q' )
order by 1 desc
;


TO_CHAR(DATE_,'YYYY-Q')
COUNT(*)


2021-4
2

2021-3
11

2021-2
8

2021-1
7

2020-4
7

2020-3
6

2020-2
8

2020-1
1


select date_
, to_char( date_, 'YYYY-Q' ) quarter
, to_char( date_, 'YY' ) || 'Q' || to_char( date_, 'Q' ) quarter_
, to_char( add_months( date_, 1 ), 'YYYY-Q' ) modified_quarter
from randomdates 
;


DATE_
QUARTER
QUARTER_
MODIFIED_QUARTER


10-APR-20
2020-2
20Q2
2020-2

16-AUG-21
2021-3
21Q3
2021-3

24-JUL-20
2020-3
20Q3
2020-3

14-APR-20
2020-2
20Q2
2020-2

13-OCT-20
2020-4
20Q4
2020-4

29-NOV-20
2020-4
20Q4
2020-4

10-FEB-21
2021-1
21Q1
2021-1

09-NOV-20
2020-4
20Q4
2020-4

27-APR-21
2021-2
21Q2
2021-2

29-SEP-20
2020-3
20Q3
2020-4

08-JAN-21
2021-1
21Q1
2021-1

02-AUG-20
2020-3
20Q3
2020-3

30-JUN-20
2020-2
20Q2
2020-3

29-AUG-21
2021-3
21Q3
2021-3

23-JUN-21
2021-2
21Q2
2021-3

07-JUL-20
2020-3
20Q3
2020-3

24-MAY-20
2020-2
20Q2
2020-2

12-SEP-21
2021-3
21Q3
2021-4

03-MAY-20
2020-2
20Q2
2020-2

07-APR-21
2021-2
21Q2
2021-2

13-OCT-20
2020-4
20Q4
2020-4

08-AUG-21
2021-3
21Q3
2021-3

30-APR-21
2021-2
21Q2
2021-2

26-OCT-21
2021-4
21Q4
2021-4

20-NOV-20
2020-4
20Q4
2020-4

24-AUG-20
2020-3
20Q3
2020-3

05-MAR-21
2021-1
21Q1
2021-2

01-FEB-21
2021-1
21Q1
2021-1

06-DEC-20
2020-4
20Q4
2021-1

05-AUG-21
2021-3
21Q3
2021-3

13-FEB-21
2021-1
21Q1
2021-1

26-DEC-20
2020-4
20Q4
2021-1

26-JUN-20
2020-2
20Q2
2020-3

18-JUL-21
2021-3
21Q3
2021-3

18-JUL-21
2021-3
21Q3
2021-3

30-AUG-21
2021-3
21Q3
2021-3

08-JUL-21
2021-3
21Q3
2021-3

12-JAN-21
2021-1
21Q1
2021-1

03-MAY-21
2021-2
21Q2
2021-2

28-MAY-20
2020-2
20Q2
2020-2

01-JAN-21
2021-1
21Q1
2021-1

06-AUG-21
2021-3
21Q3
2021-3

08-MAY-20
2020-2
20Q2
2020-2

06-NOV-21
2021-4
21Q4
2021-4

20-APR-21
2021-2
21Q2
2021-2

29-MAR-20
2020-1
20Q1
2020-2

05-SEP-20
2020-3
20Q3
2020-4

26-AUG-21
2021-3
21Q3
2021-3

16-APR-21
2021-2
21Q2
2021-2

19-JUN-21
2021-2
21Q2
2021-3


create table sitesanddates( site_, date_ )
as
select 
  trunc( dbms_random.value( 1, 6 ) )
, sysdate - dbms_random.value( 1, 600 )
from dual 
connect by level <= 50 ;
50 rows affected

select site_, to_char( date_, 'YYYY-Q' ), count(*)
from sitesanddates 
group by site_, to_char( date_, 'YYYY-Q' )
order by 1, 2
;


SITE_
TO_CHAR(DATE_,'YYYY-Q')
COUNT(*)


1
2020-2
1

1
2020-3
3

1
2020-4
1

1
2021-1
5

1
2021-2
1

1
2021-4
1

2
2020-2
2

2
2020-3
4

2
2020-4
4

2
2021-1
2

2
2021-2
2

3
2020-1
1

3
2020-3
1

3
2020-4
1

3
2021-1
2

3
2021-2
2

3
2021-4
1

4
2020-3
3

4
2021-1
1

4
2021-2
3

4
2021-3
2

4
2021-4
1

5
2020-2
1

5
2020-4
1

5
2021-1
2

5
2021-3
2


select site_, to_char( date_, 'YYYY-Q' ), count(*)
from sitesanddates 
group by rollup( site_, to_char( date_, 'YYYY-Q' ) )
order by 1, 2
;


SITE_
TO_CHAR(DATE_,'YYYY-Q')
COUNT(*)


1
2020-2
1

1
2020-3
3

1
2020-4
1

1
2021-1
5

1
2021-2
1

1
2021-4
1

1
null
12

2
2020-2
2

2
2020-3
4

2
2020-4
4

2
2021-1
2

2
2021-2
2

2
null
14

3
2020-1
1

3
2020-3
1

3
2020-4
1

3
2021-1
2

3
2021-2
2

3
2021-4
1

3
null
8

4
2020-3
3

4
2021-1
1

4
2021-2
3

4
2021-3
2

4
2021-4
1

4
null
10

5
2020-2
1

5
2020-4
1

5
2021-1
2

5
2021-3
2

5
null
6

null
null
50

DATE_	QUARTER
10-APR-20	2020-2
16-AUG-21	2021-3
24-JUL-20	2020-3
14-APR-20	2020-2
13-OCT-20	2020-4
29-NOV-20	2020-4
10-FEB-21	2021-1
09-NOV-20	2020-4
27-APR-21	2021-2
29-SEP-20	2020-3
08-JAN-21	2021-1
02-AUG-20	2020-3
30-JUN-20	2020-2
29-AUG-21	2021-3
23-JUN-21	2021-2
07-JUL-20	2020-3
24-MAY-20	2020-2
12-SEP-21	2021-3
03-MAY-20	2020-2
07-APR-21	2021-2
13-OCT-20	2020-4
08-AUG-21	2021-3
30-APR-21	2021-2
26-OCT-21	2021-4
20-NOV-20	2020-4
24-AUG-20	2020-3
05-MAR-21	2021-1
01-FEB-21	2021-1
06-DEC-20	2020-4
05-AUG-21	2021-3
13-FEB-21	2021-1
26-DEC-20	2020-4
26-JUN-20	2020-2
18-JUL-21	2021-3
18-JUL-21	2021-3
30-AUG-21	2021-3
08-JUL-21	2021-3
12-JAN-21	2021-1
03-MAY-21	2021-2
28-MAY-20	2020-2
01-JAN-21	2021-1
06-AUG-21	2021-3
08-MAY-20	2020-2
06-NOV-21	2021-4
20-APR-21	2021-2
29-MAR-20	2020-1
05-SEP-20	2020-3
26-AUG-21	2021-3
16-APR-21	2021-2
19-JUN-21	2021-2

DATE_	QUARTER	QUARTER_	MODIFIED_QUARTER
10-APR-20	2020-2	20Q2	2020-2
16-AUG-21	2021-3	21Q3	2021-3
24-JUL-20	2020-3	20Q3	2020-3
14-APR-20	2020-2	20Q2	2020-2
13-OCT-20	2020-4	20Q4	2020-4
29-NOV-20	2020-4	20Q4	2020-4
10-FEB-21	2021-1	21Q1	2021-1
09-NOV-20	2020-4	20Q4	2020-4
27-APR-21	2021-2	21Q2	2021-2
29-SEP-20	2020-3	20Q3	2020-4
08-JAN-21	2021-1	21Q1	2021-1
02-AUG-20	2020-3	20Q3	2020-3
30-JUN-20	2020-2	20Q2	2020-3
29-AUG-21	2021-3	21Q3	2021-3
23-JUN-21	2021-2	21Q2	2021-3
07-JUL-20	2020-3	20Q3	2020-3
24-MAY-20	2020-2	20Q2	2020-2
12-SEP-21	2021-3	21Q3	2021-4
03-MAY-20	2020-2	20Q2	2020-2
07-APR-21	2021-2	21Q2	2021-2
13-OCT-20	2020-4	20Q4	2020-4
08-AUG-21	2021-3	21Q3	2021-3
30-APR-21	2021-2	21Q2	2021-2
26-OCT-21	2021-4	21Q4	2021-4
20-NOV-20	2020-4	20Q4	2020-4
24-AUG-20	2020-3	20Q3	2020-3
05-MAR-21	2021-1	21Q1	2021-2
01-FEB-21	2021-1	21Q1	2021-1
06-DEC-20	2020-4	20Q4	2021-1
05-AUG-21	2021-3	21Q3	2021-3
13-FEB-21	2021-1	21Q1	2021-1
26-DEC-20	2020-4	20Q4	2021-1
26-JUN-20	2020-2	20Q2	2020-3
18-JUL-21	2021-3	21Q3	2021-3
18-JUL-21	2021-3	21Q3	2021-3
30-AUG-21	2021-3	21Q3	2021-3
08-JUL-21	2021-3	21Q3	2021-3
12-JAN-21	2021-1	21Q1	2021-1
03-MAY-21	2021-2	21Q2	2021-2
28-MAY-20	2020-2	20Q2	2020-2
01-JAN-21	2021-1	21Q1	2021-1
06-AUG-21	2021-3	21Q3	2021-3
08-MAY-20	2020-2	20Q2	2020-2
06-NOV-21	2021-4	21Q4	2021-4
20-APR-21	2021-2	21Q2	2021-2
29-MAR-20	2020-1	20Q1	2020-2
05-SEP-20	2020-3	20Q3	2020-4
26-AUG-21	2021-3	21Q3	2021-3
16-APR-21	2021-2	21Q2	2021-2
19-JUN-21	2021-2	21Q2	2021-3