0

I am trying to count business days between a certain date range in PostgreSQL.

My table:

|start_date |end_date |
------------------------
|2017-06-01 |2017-06-01| 
|2017-05-29 |2017-06-02| 

This is my code:

SELECT pto.start_date, pto.end_date, 
SUM(CASE WHEN extract (dow FROM foo) IN(1,2,3,4,5) THEN 1 ELSE 0 END) as theDIFF 
FROM (
  SELECT start_date, (start_date::date + (generate_series(0,end_date::date 
        - start_date::date)||'days')::interval) AS foo
    FROM pto
) foo inner join pto pto
on pto.start_date = foo.start_date 
group by pto.start_date, pto.end_date

My OUTPUT:

      |start_date(date)| end_date(date) |theDiff(integer)
      ---------------------------------------------------
      |2017-06-01      |  2017-06-01    |        29     |
      |2017-05-29      |  2017-06-02    |        12     |
      ---------------------------------------------------

Expected Output:

      |start_date(date)| end_date(date) |theDiff(integer)
      ---------------------------------------------------
      |2017-06-01      |  2017-06-01    |        1      |
      |2017-05-29      |  2017-06-02    |        5      |
      ---------------------------------------------------
2
  • Is there any id or PK column?
    – McNets
    May 24 '18 at 7:28
  • why do you want to count instead of just computing it?
    – Jasen
    May 26 '18 at 10:21
1

You're not using (or showing us) a unique key, if you have more than one row with the same (start_date, end_date) you will get wrong results.

select start_date, end_date, 
       sum(case when extract (dow from dt) in (1,2,3,4,5) then 1 else 0 end) as thediff
from (
       select start_date, end_date, 
              generate_series(start_date, end_date, '1 day'::interval) as dt
       from   tbl
     ) t
group by start_date, end_date;
start_date          | end_date            | thediff
:------------------ | :------------------ | ------:
2017-05-29 00:00:00 | 2017-06-02 00:00:00 |       5
2017-06-01 00:00:00 | 2017-06-01 00:00:00 |       1
select id, start_date, end_date, 
       sum(case when extract (dow from dt) in (1,2,3,4,5) then 1 else 0 end) as thediff
from (
       select id, start_date, end_date, 
              generate_series(start_date, end_date, '1 day'::interval) as dt
       from   tbl
     ) t
group by id, start_date, end_date;
id | start_date          | end_date            | thediff
-: | :------------------ | :------------------ | ------:
 1 | 2017-06-01 00:00:00 | 2017-06-01 00:00:00 |       1
 2 | 2017-05-29 00:00:00 | 2017-06-02 00:00:00 |       5

db<>fiddle here

1

Here is a function for calculating Business Days between two dates simmilar to networkdays excel formula. Doesn't support Holiday list yet.

create or replace function count_business_days(from_date date, to_date date)
returns bigint
as $fbd$
    select count(d::date) as d
    from generate_series(from_date, to_date, '1 day'::interval) d
    where extract('dow' from d) not in (0, 6) 
$fbd$ language sql;

Usage

SELECT issue_id, start_date, live_date, count_business_days(
    TO_DATE(start_date, 'YYYY-MM-DD'),
    TO_DATE(live_date, 'YYYY-MM-DD')
    ) as businessdays
    FROM atable;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.