0

I have the below table showing employee leave days between two dates

+--------------+-----------+-----------+
| EMPLOYEENAME | BEGINDATE |  ENDDATE  |
+--------------+-----------+-----------+
| John         | 06/JUN/18 | 06/JUN/18 |
| Paul         | 25/JUN/18 | 06/JUL/18 |
| Jill         | 23/APR/18 | 10/AUG/17 |
| Mary         | 01/MAR/18 | 20/JUN/18 |
| Mary         | 21/JUN/18 | 31/OCT/18 |
+--------------+-----------+-----------+

Id like to know how many business leave days the employee has taken for a particular month, in this example JUN-18. I'm expecting to see the below result:

+--------------+--------+-------------+
| EMPLOYEENAME | MONTH  | DAYS_ABSENT |
+--------------+--------+-------------+
| John         | JUN-18 |           1 |
| Paul         | JUN-18 |           5 |
| Jill         | JUN-18 |          21 |
| Mary         | JUN-18 |          21 |
+--------------+--------+-------------+

Prefer to use SQL/PLSQL but last resort can use Python to do this.

Tikkaty
  • 772
  • 1
  • 8
  • 24
  • You can do this in Oracle, but I think you would need to use a calendar table. Also, are you taking holidays into account here? – Tim Biegeleisen Aug 02 '18 at 01:59
  • This can help you I believe : https://stackoverflow.com/questions/252519/count-work-days-between-two-dates – BERTRAND NOEL Aug 02 '18 at 02:03
  • This has been asked many, many times since the early days of relational databases. Here's Tom's answer (the famous Tom from Oracle land) from May 2000: [Ask TOM: "Counting the number of business days between 2 dates"](https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:185012348071) – hunteke Aug 02 '18 at 03:09
  • Closed due to duplication. Used the solution by Ask Tom above – Tikkaty Aug 02 '18 at 03:30

0 Answers0