How to create a table containing all dates in a given year that are within a 7-day range of known da-CodePudding

I have a table (holidays) containing all the holidays in a year:

I need to make a table (t1) where 1 column contains all the 365 days within a year, and another column 'description' containing a name of a holiday for all dates that are within a /- 7 day range of a holiday, and 'None' for all dates that are outside of this range.

I was able to use generate_series to create the first-column (my_date: all 365 days within a year) and used a left-join to try to create the second column (description)

WITH all_dates AS
    SELECT my_date::date
    FROM generate_series('2020-01-01', '2020-12-31', '1 day'::interval) my_date)
 
SELECT  all_dates.my_date,
        holidays.description
FROM all_dates
LEFT JOIN holidays
        ON all_dates.my_date = holidays.holiday_date

To create the table below.

However, I need it to be such that 2020-01-02, 2020-01-02, ..., 2020-01-08 would have the description 'New Year's Day' as these dates are within a 7-day range of New Year's Day, and so forth for other days within a 7-day range of other holidays (e.g., rows for dates between 2020-12-18 to 2020-12-31 would have the description 'Christmas')

I'm also unsure about how to handle days that are within a 7-day range of more than one holiday (Father's Day and Independence Day have overlapping dates that are within a 7-day range). I need it to be such that there is only one row per day in 2020.

Any help would be appreciated!

CodePudding user response：

Try something like below. You already have the ranges around the holidays, so use that in the join. Then, use distinct on to restrict results to one per calendar date, and adjust the order by to keep the holiday you want.

with all_dates as (
  select my_date::date
    from generate_series('2020-01-01', 
                         '2020-12-31', '1 day'::interval) my_date
)
select distinct on (all_dates.my_date) 
       all_dates.my_date,
       holidays.description
  from all_dates
       left join holidays
         on all_dates.my_date 
              between holidays.holiday_date_min7
                  and holidays.holiday_date_plus7
 order by all_dates.my_date, 
          abs(all_dates.my_date - holidays.holiday_date), -- keep closest
          all_dates.my_date - holidays.holiday_date -- prefer upcoming holiday