I am trying to count business days between a certain date range in PostgreSQL.
SELECT
SUM(CASE WHEN extract (dow FROM foo) IN(1,2,3,4,5) THEN 1 ELSE 0
END)
FROM (SELECT ('2007-04-01'::date +
(generate_series(0,'2007-04-30'::date
- '2007-04-01'::date)||'days')::interval) AS foo) foo
I want to replace the dates with start_date
and end_date
from a table named myTable
start_date
and end_date
format is yyyy-mm-dd
Actually I need it to display the business days date diff for each row
|start_date |end_date |
------------------------
|2018-04-01 |2018-04-30|
|2018-05-01 |2018-05-30|
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 |
---------------------------------------------------
Your example code is a bit confusing, so just considering business days and your table, this will give expected output:
CREATE TABLE myTable (start_date date, end_date date);
INSERT INTO myTable VALUES('2017-06-01', '2017-06-01'),('2017-05-29', '2017-06-02');
SELECT start_date, end_date,
SUM(CASE WHEN EXTRACT(dow FROM days) BETWEEN 1 AND 5 THEN 1 ELSE 0 END)
FROM myTable
CROSS JOIN LATERAL generate_series(start_date, end_date, interval '1 day') AS days
GROUP BY start_date, end_date;
It will generate rows, each having start_date, end_date and one day in between those. Then it will agregate by start_date, end_date and SUM() dates that have day of week between 1 and 5 (monday-friday).
Without aggregation it looks like this:
start_date | end_date | days | dow
------------+------------+------------------------+-----
2017-06-01 | 2017-06-01 | 2017-06-01 00:00:00+02 | 4
2017-05-29 | 2017-06-02 | 2017-05-29 00:00:00+02 | 1
2017-05-29 | 2017-06-02 | 2017-05-30 00:00:00+02 | 2
2017-05-29 | 2017-06-02 | 2017-05-31 00:00:00+02 | 3
2017-05-29 | 2017-06-02 | 2017-06-01 00:00:00+02 | 4
2017-05-29 | 2017-06-02 | 2017-06-02 00:00:00+02 | 5
And group by:
start_date | end_date | sum
------------+------------+-----
2017-06-01 | 2017-06-01 | 1
2017-05-29 | 2017-06-02 | 5