Adding custom column to calendar table containing the year number of the following year on and after 2nd Sunday of December of each year

Question

I have created the following calendar table:

WITH dates AS (
    SELECT EXPLODE(SEQUENCE(TO_DATE('1970-01-01'), TO_DATE('2100-12-31'), INTERVAL 1 DAY)) AS calendar_date
),

calendar_table AS (
    SELECT
      YEAR(calendar_date) * 10000 + MONTH(calendar_date) * 100 + DAY(calendar_date) AS date_integer,
      calendar_date,
      YEAR(calendar_date) AS year_of_date,
      QUARTER(calendar_date) AS quarter_of_year,
      MONTH(calendar_date) AS month_of_year,
      DAY(calendar_date) AS day_of_month,
      WEEKDAY(calendar_date) + 1 AS day_of_week_start_monday,
      DAYOFWEEK(calendar_date) AS day_of_week_start_sunday,
      CASE
        WHEN DAY(calendar_date) >= 1 AND DAY(calendar_date) <= 7 THEN 1
        WHEN DAY(calendar_date) >= 8 AND DAY(calendar_date) <= 14 THEN 2
        WHEN DAY(calendar_date) >= 15 AND DAY(calendar_date) <= 21 THEN 3
        WHEN DAY(calendar_date) >= 22 AND DAY(calendar_date) <= 28 THEN 4
        ELSE 5
      END AS day_of_week_ordinal,
      CASE
        WHEN WEEKDAY(calendar_date) < 5 THEN TRUE
        ELSE FALSE
      END AS is_week_day,
      CASE
        WHEN WEEKDAY(calendar_date) > 4 THEN TRUE
        ELSE FALSE
      END AS is_weekend,
      CASE
        WHEN calendar_date = DATE_TRUNC('month', calendar_date)::DATE THEN TRUE
        ELSE FALSE
      END AS is_first_day_of_month,
      CASE
        WHEN calendar_date = LAST_DAY(calendar_date) THEN TRUE
        ELSE FALSE
      END AS is_last_day_of_month,
      DAYOFYEAR(calendar_date) AS day_of_year,
      WEEKOFYEAR(calendar_date) AS iso_week_of_year,
      EXTRACT(YEAROFWEEK FROM calendar_date) AS iso_year_of_date,
    FROM
      dates
)

I am missing a custom calendar column that would abide by the following rule:

From the second Sunday (inclusive) in December of each year, the column should contain a concatenation of 'X' and the year number of the following year.

Example:

calendar_date	custom_column
2022-12-10	X2022
2022-12-11	X2023
2022-12-12	X2023
...	...
2023-12-09	X2023
2023-12-10	X2024
2023-12-11	X2024

So far, I've been able to identify the second Sunday of December in each year by combining the logic behind the columns month_of_year, day_of_week_ordinal and day_of_week_start_monday in my calendar table, but I fail to grasp any implementation (I'm sure I'm missing something simple here).

I can calculate a flag for the second Sunday in December of each year by utilising the following logic:

CASE
    WHEN
        month_of_year = 12
        AND day_of_week_ordinal = 2
        AND day_of_week_start_monday = 7 THEN TRUE
    ELSE FALSE
END AS second_sunday_in_month

But I fail to see how I can get transfer this logic to what I want as the end result.

Edit: I have added a PostgreSQL fiddle as an interactive example.

If you know the date of the second Sunday in December, set a flag to 0 for every date in the year prior to that date, and a 1 for every date after it (on a per-year basis). IF that flag is 0, then your column would be X + YEAR(date). If it's 1, then the new column would be X + YEAR(date) + 1. Am I missing something? — SchmitzIT, Nov 22 '22 at 11:09
Thank you for your comment. I still fail to see how I would implement this in code. I believe it is very simple, but I simply cannot get it to work. I've created a fiddle (https://dbfiddle.uk/InFbleIR). If you have a solution, I would be grateful for any hint. — QueryingQuail, Nov 22 '22 at 12:02

Epitay · Accepted Answer · 2022-11-22T17:28:27.830

Here this might be able to help you :

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' || date_part('year', calendar_date) +1
ELSE 'X' || date_part('year', calendar_date)

END AS is_second_sunday_of_december FROM calendar_table;

https://dbfiddle.uk/WynGf5w_

Problem is its only working on yearly basis so there might be more tweaking needed!

Update:

There you go:

  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

fiddle

i hope this time the link works if not there is an underscore on the end.

Thank you for your answer. You dbfiddle link seems to not work, but I checked the results manually and they are indeed closer to what I would like to accomplish. Sadly, the year number is incorrect once we enter a new year (in this example '2024' on '2023-01-01' where it should be '2023' - as you also mention). — QueryingQuail, Nov 22 '22 at 15:21
Yeah fiddle got me an underscore as last and the link can't handle it. lets think a little more about it, there might be a solution for you — Epitay, Nov 22 '22 at 15:31
I think there might be optimisations lurking in this approach, but the logic works out. Thank you for the contribution. — QueryingQuail, Nov 23 '22 at 09:06

Adding custom column to calendar table containing the year number of the following year on and after 2nd Sunday of December of each year

1 Answers1