Auto Weekend Generator – Holiday Calander (Advance)

What are we going to learn

• Generate_series function in Postgres
• Auto identify weekends for a given date period
• Calculate the next working day
• Get All Days except Weekends and Holidays

Please use the Data set and the table structure from the previous post (previous post).

In a previous post, we did the similar kind of a function to generate next working day from a holiday calendar table. In there we kept all the holiday and weekends inside a table. (previous post) . So, all the time user must maintain the weekends inside the table which is a time consuming process.

Using this new DB function, system will automatically generates the weekends without a user involvement by using the generate_serial inbuilt function in the postgress DB.

Sample Query

SELECT   date_trunc('day', dd)::date WEEKENDS
FROM   generate_series
        ( '2022-04-04'::timestamp 
        , '2022-04-20'::timestamp
        , '1 day'::interval) dd
       where EXTRACT(ISODOW from dd) in (6,7)

According to the query, the system will identify the date period and provide the series of the dates in between the requested dates. Once the date range is received we filter the data set through the Where clause using weekdays in 6 and 7, which is Saturdays and Sundays.

Please use the same table structure and the data set used in the previous post (go to the previous post)

Sample Function call

Input:

select * from set_fun_compairecalender_autoweekends(’25-MAY-2022′, 1, 2);

Output:

2022-05-30

Auto Weekend Calander function

CREATE OR REPLACE FUNCTION set_fun_compairecalender_autoweekends(_start_date timestamp without time zone DEFAULT now(), _company integer DEFAULT 2, _country integer DEFAULT 1)
 RETURNS timestamp without time zone
 LANGUAGE plpgsql
AS $function$
declare 
	dte timestamp;
begin
	
         	select coalesce(hcl_date,'1900-01-01') into dte from (
		/* Auto generate Weekends*/
		SELECT (ROW_NUMBER()  OVER(ORDER BY dd)::integer)*-1 hcl_id , date_trunc('day', dd)::date hcl_date
		FROM   generate_series
		        ( _start_date::timestamp 
		        , _start_date::timestamp
		        , '1 day'::interval) dd
		       where EXTRACT(ISODOW from dd) in (6,7)
		union
		/*Load holidays from Calander*/		
		  select hcl_id, coalesce(hcl_date,'1900-01-01')  from holiday_calender hc where  hc.hcl_country = _country  and 
		  hc.hcl_company  = _company  and hc.hcl_date::date = _start_Date::date) A order by hcl_id asc  limit 1 ;
      
	
  if coalesce(dte,'1900-01-01') <> '1900-01-01' then 
    -- Recursive function call at here
    select set_fun_compairecalender_autoweekends into dte from set_fun_compairecalender_autoweekends(dte::date + (' 1 day')::interval,_company,     _country);

  end if;

  return coalesce(dte,_start_Date)::date;

end
$function$
;    

How the function work

WHY USED (ROW_NUMBER() OVER(ORDER BY dd)::integer)*-1 hcl_id

We received our data as a virtual table. So you do not have a unique key to identify your record. As a precaution, I used Row number function to generate the uniqueness. But this value may conflict with the holiday table primary key (hcl_id). To avoid this issue, I multiply the value by -1. So we can receive a unique key.

WHAT HAPPENING WITH

 generate_series ( _start_date::timestamp , _start_date::timestamp , '1 day'::interval) dd

If you run the above code like this, select * from generate_series ( '2022-05-28':timestamp , '2022-05-28'::timestamp , '1 day'::interval) dd, you can get the wole list of dates MAY 28, 29 and 30th as a series. So this is the key area that we are using to generate our auto weekends.

HOW WE FILTER WEEKENDS ONLY

simply you can do it by  

select * from generate_series ( '2022-05-28':timestamp , '2022-05-28'::timestamp , '1 day'::interval) dd where EXTRACT(ISODOW from dd) in (6,7)  

EXTRACT(ISODOW from dd) <-- this code will help you to extract the day of the week from a given date.

day 6 and 7 mean, Saturday and Sunday. So by adding the following area as the where clause, EXTRACT(ISODOW from dd) in (6,7)  it filters the weekends only.

WHY UNION WITH HOLIDAY_CALENDAR TABLE

You can see that we put our virtual data set and the holiday_calendar table into one data set by using the union keyword. From that, we can cater our all data sets as one table. So we used this data set as a virtual table as “A” like

select coalesce(hcl_date,‘1900-01-01’) into dte from (……our subquery with union …) A order by hcl_id asc limit 1 ;

So, the system will return you only one date from the system by adding the limit 1 and, order by ascending clause help us to bring the lowest unique key into the top. So it always gives its priority to weekend type dates because our weekend data unique key is always created with a minus (-) value. If you need to give your priority to the holciday_calender table, you can just simply change the order by clause to descending.

Get All working dates except Holidays and Weekends

We can use the below query to get the dates except for the holidays and the weekends by using below query

select date_trunc('day', wd)::date WORKING_DATES from 
generate_series
        ( '2022-04-04'::timestamp 
        , '2022-04-20'::timestamp
        , '1 day'::interval) wd
 where wd::date not in (
SELECT  date_trunc('day', dd)::date WEEKENDS
FROM   generate_series
        ( '2022-04-04'::timestamp 
        , '2022-04-20'::timestamp
        , '1 day'::interval) dd
       where EXTRACT(ISODOW from dd) in (6,7)
union        
SELECT  hcl_date::date
FROM public.holiday_calender
where hcl_date between '2022-04-04' and '2022-05-30');