Hi friends,
We have a table as below:
| EMP_NO | DAY_OF_WEEK | FROM_TIME | TO_TIME |
| 1001 | 0 | 09:00 | 17:00 |
| 1001 | 1 | 09:00 | 17:00 |
| 1001 | 2 | 09:00 | 17:00 |
| 1001 | 3 | 09:00 | 17:00 |
| 1001 | 4 | 09:00 | 17:00 |
| 1001 | 5 | 12:00 | 17:00 |
| 1001 | 6 | 00:00 | 00:00 |
DAY_OF_WEEK: 0 - Monday, 1 - Tuesday,... 6 - Sunday
TIME columns: HH24:MI format
Need to calculate total available minutes for the given date range in SQL query. For example,
Case 1: From 27-Jul-2020 (Monday) to 02-Aug-2020 (Sunday), the employee 1001's total available hours should be 2700 (i.e., (8 hr * 5 days) + (5 hr * 1 day) = 2400 + 300 = 2700 minutes)
Case 2: From 27-Jul-2020 (Monday) to 28-Jul-2020 (Tuesday), the employee 1001's total available hours should be 960 (i.e., (8 hr * 2 days) = 960 minutes)
Help would be appreciated! Thanks in advance!
Table script:
CREATE TABLE emp_availablity
(
emp_no NUMBER(5),
day_of_week NUMBER(1),
from_time VARCHAR2(20),
to_time VARCHAR2(20)
);
INSERT INTO emp_availablity (emp_no, day_of_week, from_time, to_time)
values (1001,0,'09:00','17:00');
INSERT INTO emp_availablity (emp_no, day_of_week, from_time, to_time)
VALUES (1001,1,'09:00','17:00');
INSERT INTO emp_availablity (emp_no, day_of_week, from_time, to_time)
values (1001,2,'09:00','17:00');
INSERT INTO emp_availablity (emp_no, day_of_week, from_time, to_time)
values (1001,3,'09:00','17:00');
INSERT INTO emp_availablity (emp_no, day_of_week, from_time, to_time)
VALUES (1001,4,'09:00','17:00');
INSERT INTO emp_availablity (emp_no, day_of_week, from_time, to_time)
values (1001,5,'12:00','17:00');
INSERT INTO emp_availablity (emp_no, day_of_week, from_time, to_time)
values (1001,6,'00:00','00:00');
SELECT * FROM emp_availablity order by day_of_week;