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 ONLYsimply 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');