CODEWITHSUNDEEP
sqlpostgresqldatetimecountsql-function
Robert Riley
Robert Riley

Reputation: 406

Counting Business Days PostgresSQL

I am attempting to do something like this:

SELECT t.id, t.startdate, t.enddate, FOO
FROM table

Where "Foo" is some sql mumbo-jumbo to calculate the business days between startdate and enddate like such:

+----+------------+------------+--------------+
| id | startdate  |  enddate   | businessdays |
+----+------------+------------+--------------+
| 1  | 2020-09-12 | 2020-09-28 |     11       | 
| 2  | 2020-09-02 | 2020-09-28 |     19       | 
| 3  | 2020-09-22 | 2020-09-28 |     5        | 
+----+------------+------------+--------------+

I was able to create a function to do so with no trouble:

CREATE OR REPLACE FUNCTION getbusinessday(startdate date, enddate date DEFAULT current_date) RETURNS int
LANGUAGE plpgsql
AS
$$
DECLARE
    daycount integer;
BEGIN
    SELECT COUNT(i) 
    INTO daycount
    FROM generate_series(startdate,enddate,'1 day') i 
    WHERE EXTRACT(DOW FROM i) NOT IN (0, 6);
    RETURN daycount;
END;
$$;

Is there anyway I can achieve this without having to use this function?

Bonus Question for Bonus Points:

Would it be possible to allow for a timestamp to be entered and just have it cast to date within the actual function? I'm just curious.

Upvotes: 0

Views: 58

Answers (1)

GMB
GMB

Reputation: 222702

You can replace the function with a lateral join:

select t.*, x.*
from mytable t
cross join lateral (
    select count(*) businessdays
    from generate_series(t.startdate, coalesce(t.enddate, current_date), '1 day') x(dt)
    where extract (dow from x.dt) not in (0, 6)
) x

I am unsure about your question regarding the timestamp/date casting. If your columns have a time component and you don't want to take it into account, then you can cast in generate_series():

from generate_series(t.startdate::date, coalesce(t.enddate::date, current_date), '1 day') x(dt)

Upvotes: 2

Related Questions