Postgres 15

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

select version();


version


PostgreSQL 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit



SELECT 1


WITH dates AS (
	SELECT GENERATE_SERIES('2023-12-01', '2024-01-01', '1 day'::interval)::DATE AS calendar_date
),

calendar_table AS (
  SELECT
    calendar_date,
    EXTRACT(YEAR FROM calendar_date) AS year_of_date,
    EXTRACT(MONTH FROM calendar_date) AS month_of_year,
    EXTRACT(ISODOW FROM calendar_date) AS day_of_week_start_monday,
    CASE
      WHEN EXTRACT(DAY FROM calendar_date) >= 1 AND EXTRACT(DAY FROM calendar_date) <= 7 THEN 1
	  WHEN EXTRACT(DAY FROM calendar_date) >= 8 AND EXTRACT(DAY FROM calendar_date) <= 14 THEN 2
	  WHEN EXTRACT(DAY FROM calendar_date) >= 15 AND EXTRACT(DAY FROM calendar_date) <= 21 THEN 3
	  WHEN EXTRACT(DAY FROM calendar_date) >= 22 AND EXTRACT(DAY FROM calendar_date) <= 28 THEN 4
	  ELSE 5
    END AS day_of_week_ordinal
  FROM dates
)
 SELECT
  *,
  CASE
    WHEN calendar_date between (select calendar_date from calendar_table
      where month_of_year = 12 -- December
      AND day_of_week_start_monday = 7 -- Sunday
      AND day_of_week_ordinal = 2 ) and (select date_trunc('year',calendar_date + interval '1 year') - interval '1 day')
    THEN 'X'
  When calendar_date > (select date_trunc('year',calendar_date + interval '1 year') - interval '1 day')
   then 'y'
  else 'y'
  END AS is_second_sunday_of_december
FROM calendar_table;


calendar_date
year_of_date
month_of_year
day_of_week_start_monday
day_of_week_ordinal
is_second_sunday_of_december


2023-12-01
2023
12
5
1
y

2023-12-02
2023
12
6
1
y

2023-12-03
2023
12
7
1
y

2023-12-04
2023
12
1
1
y

2023-12-05
2023
12
2
1
y

2023-12-06
2023
12
3
1
y

2023-12-07
2023
12
4
1
y

2023-12-08
2023
12
5
2
y

2023-12-09
2023
12
6
2
y

2023-12-10
2023
12
7
2
X

2023-12-11
2023
12
1
2
X

2023-12-12
2023
12
2
2
X

2023-12-13
2023
12
3
2
X

2023-12-14
2023
12
4
2
X

2023-12-15
2023
12
5
3
X

2023-12-16
2023
12
6
3
X

2023-12-17
2023
12
7
3
X

2023-12-18
2023
12
1
3
X

2023-12-19
2023
12
2
3
X

2023-12-20
2023
12
3
3
X

2023-12-21
2023
12
4
3
X

2023-12-22
2023
12
5
4
X

2023-12-23
2023
12
6
4
X

2023-12-24
2023
12
7
4
X

2023-12-25
2023
12
1
4
X

2023-12-26
2023
12
2
4
X

2023-12-27
2023
12
3
4
X

2023-12-28
2023
12
4
4
X

2023-12-29
2023
12
5
5
X

2023-12-30
2023
12
6
5
X

2023-12-31
2023
12
7
5
X

2024-01-01
2024
1
1
1
X



SELECT 32


WITH dates AS (
	SELECT GENERATE_SERIES('2023-12-01', '2024-01-01', '1 day'::interval)::DATE AS calendar_date
),

calendar_table AS (
  SELECT
    calendar_date,
    EXTRACT(YEAR FROM calendar_date) AS year_of_date,
    EXTRACT(MONTH FROM calendar_date) AS month_of_year,
    EXTRACT(ISODOW FROM calendar_date) AS day_of_week_start_monday,
    CASE
      WHEN EXTRACT(DAY FROM calendar_date) >= 1 AND EXTRACT(DAY FROM calendar_date) <= 7 THEN 1
	  WHEN EXTRACT(DAY FROM calendar_date) >= 8 AND EXTRACT(DAY FROM calendar_date) <= 14 THEN 2
	  WHEN EXTRACT(DAY FROM calendar_date) >= 15 AND EXTRACT(DAY FROM calendar_date) <= 21 THEN 3
	  WHEN EXTRACT(DAY FROM calendar_date) >= 22 AND EXTRACT(DAY FROM calendar_date) <= 28 THEN 4
	  ELSE 5
    END AS day_of_week_ordinal
  FROM dates)
select * ,
  CASE
    WHEN 
      year_of_date = (select year_of_date from calendar_table where month_of_year = 12 -- December
      AND day_of_week_start_monday = 7 -- Sunday
      AND day_of_week_ordinal = 2)  
      AND calendar_date between (select calendar_date from calendar_table
      where month_of_year = 12 -- December
      AND day_of_week_start_monday = 7 -- Sunday
      AND day_of_week_ordinal = 2 ) and (select date_trunc('year',calendar_date + interval '1 year') - interval '1 day')
    THEN 'X' || date_part('year', calendar_date) + 1
    else 'X' || date_part('year', calendar_date)
    END AS is_second_sunday_of_december
  from calendar_table 
  


calendar_date
year_of_date
month_of_year
day_of_week_start_monday
day_of_week_ordinal
is_second_sunday_of_december


2023-12-01
2023
12
5
1
X2023

2023-12-02
2023
12
6
1
X2023

2023-12-03
2023
12
7
1
X2023

2023-12-04
2023
12
1
1
X2023

2023-12-05
2023
12
2
1
X2023

2023-12-06
2023
12
3
1
X2023

2023-12-07
2023
12
4
1
X2023

2023-12-08
2023
12
5
2
X2023

2023-12-09
2023
12
6
2
X2023

2023-12-10
2023
12
7
2
X2024

2023-12-11
2023
12
1
2
X2024

2023-12-12
2023
12
2
2
X2024

2023-12-13
2023
12
3
2
X2024

2023-12-14
2023
12
4
2
X2024

2023-12-15
2023
12
5
3
X2024

2023-12-16
2023
12
6
3
X2024

2023-12-17
2023
12
7
3
X2024

2023-12-18
2023
12
1
3
X2024

2023-12-19
2023
12
2
3
X2024

2023-12-20
2023
12
3
3
X2024

2023-12-21
2023
12
4
3
X2024

2023-12-22
2023
12
5
4
X2024

2023-12-23
2023
12
6
4
X2024

2023-12-24
2023
12
7
4
X2024

2023-12-25
2023
12
1
4
X2024

2023-12-26
2023
12
2
4
X2024

2023-12-27
2023
12
3
4
X2024

2023-12-28
2023
12
4
4
X2024

2023-12-29
2023
12
5
5
X2024

2023-12-30
2023
12
6
5
X2024

2023-12-31
2023
12
7
5
X2024

2024-01-01
2024
1
1
1
X2024



SELECT 32

calendar_date	year_of_date	month_of_year	day_of_week_start_monday	day_of_week_ordinal	is_second_sunday_of_december
2023-12-01	2023	12	5	1	y
2023-12-02	2023	12	6	1	y
2023-12-03	2023	12	7	1	y
2023-12-04	2023	12	1	1	y
2023-12-05	2023	12	2	1	y
2023-12-06	2023	12	3	1	y
2023-12-07	2023	12	4	1	y
2023-12-08	2023	12	5	2	y
2023-12-09	2023	12	6	2	y
2023-12-10	2023	12	7	2	X
2023-12-11	2023	12	1	2	X
2023-12-12	2023	12	2	2	X
2023-12-13	2023	12	3	2	X
2023-12-14	2023	12	4	2	X
2023-12-15	2023	12	5	3	X
2023-12-16	2023	12	6	3	X
2023-12-17	2023	12	7	3	X
2023-12-18	2023	12	1	3	X
2023-12-19	2023	12	2	3	X
2023-12-20	2023	12	3	3	X
2023-12-21	2023	12	4	3	X
2023-12-22	2023	12	5	4	X
2023-12-23	2023	12	6	4	X
2023-12-24	2023	12	7	4	X
2023-12-25	2023	12	1	4	X
2023-12-26	2023	12	2	4	X
2023-12-27	2023	12	3	4	X
2023-12-28	2023	12	4	4	X
2023-12-29	2023	12	5	5	X
2023-12-30	2023	12	6	5	X
2023-12-31	2023	12	7	5	X
2024-01-01	2024	1	1	1	X

calendar_date	year_of_date	month_of_year	day_of_week_start_monday	day_of_week_ordinal	is_second_sunday_of_december
2023-12-01	2023	12	5	1	X2023
2023-12-02	2023	12	6	1	X2023
2023-12-03	2023	12	7	1	X2023
2023-12-04	2023	12	1	1	X2023
2023-12-05	2023	12	2	1	X2023
2023-12-06	2023	12	3	1	X2023
2023-12-07	2023	12	4	1	X2023
2023-12-08	2023	12	5	2	X2023
2023-12-09	2023	12	6	2	X2023
2023-12-10	2023	12	7	2	X2024
2023-12-11	2023	12	1	2	X2024
2023-12-12	2023	12	2	2	X2024
2023-12-13	2023	12	3	2	X2024
2023-12-14	2023	12	4	2	X2024
2023-12-15	2023	12	5	3	X2024
2023-12-16	2023	12	6	3	X2024
2023-12-17	2023	12	7	3	X2024
2023-12-18	2023	12	1	3	X2024
2023-12-19	2023	12	2	3	X2024
2023-12-20	2023	12	3	3	X2024
2023-12-21	2023	12	4	3	X2024
2023-12-22	2023	12	5	4	X2024
2023-12-23	2023	12	6	4	X2024
2023-12-24	2023	12	7	4	X2024
2023-12-25	2023	12	1	4	X2024
2023-12-26	2023	12	2	4	X2024
2023-12-27	2023	12	3	4	X2024
2023-12-28	2023	12	4	4	X2024
2023-12-29	2023	12	5	5	X2024
2023-12-30	2023	12	6	5	X2024
2023-12-31	2023	12	7	5	X2024
2024-01-01	2024	1	1	1	X2024