CODEWITHSUNDEEP
sqlpostgresql
SYAHRIL ASIDIQ FIRDAUS
SYAHRIL ASIDIQ FIRDAUS

Reputation: 41

Postgresql How to Calculate between 2 date depends on another table

let's say i have two table like this :

workday_emp

emp_id    work_start       work_end
  1      "2021-04-06"     "2021-04-14"
  2      "2021-04-27"     "2021-05-04"
  3      "2021-04-30"     "2021-05-07"

holiday_tbl

id       name          date
1      "holiday 1"    "2021-04-07"
2      "holiday 2"    "2021-04-28"
3      "holiday 3"    "2021-04-29"

i want to show table like this with a query:

emp_id    work_start       work_end        day_holiday
  1      "2021-04-06"     "2021-04-14"          1
  2      "2021-04-27"     "2021-05-04"          2
  3      "2021-04-30"     "2021-05-07"          1

the question is, how to calculate how many "day_holiday" between "work_start" and "work_end" depends to "holiday_tbl" table?

Upvotes: 1

Views: 169

Answers (2)

Maciej Los
Maciej Los

Reputation: 8591

This should do the job:

SELECT emp_id, work_start, work_end, COUNT(ht.holiday) holiday_cnt
FROM workday_emp we LEFT JOIN 
(
  SELECT date holiday
  FROM holiday_tbl 
) ht ON ht.holiday BETWEEN we.work_start AND we.work_end
GROUP BY 1, 2, 3
ORDER BY 1, 2;

db<>fiddle

Upvotes: 0

Rahul Biswas
Rahul Biswas

Reputation: 3467

Please try this. For Employee 3 holiday count will 0 not 1 because his work_day starts at april30 but last holiday was apr29.

-- PostgreSQL(v11)
SELECT w.emp_id, w.work_start, w.work_end
     , (SELECT COUNT(id) 
        FROM holiday_tbl 
        WHERE holiday_date BETWEEN w.work_start AND w.work_end) day_holiday
FROM workday_emp w

Please check from url https://dbfiddle.uk/?rdbms=postgres_11&fiddle=1948691b58ba841b2765d7de383f8df8

Upvotes: 1

Related Questions