i have same problem like here
but little different. i have 2 table like this :
table employee
emp_id emp_name emp_birth_date
123456 sacha 18/07/1980
Using this query
create table employee (emp_id char(10), emp_name char(10), emp_birth_date date);
insert into employee values ('123456', 'sacha', (TO_DATE('18/07/1980', 'dd/mm/yyyy')));
and table dept
table dept
emp_id emp_reg_date emp_time_in emp_time_off
123456 25/12/2011 10:00:00 19:00:00
using this query
create table dept (emp_id char(10), emp_reg_date date, emp_time_in char(10), emp_time_off char(10));
insert into dept values ('123456', (TO_DATE('25/12/2011', 'dd/mm/yyyy')), '10:00:00', '19:00');
all data type is char except birth_date and reg_date
i can display emp_id, emp_name, emp_reg_date, emp_time_in, emp_time_off using this query select employee.emp_id, employee.emp_name, dept.emp_date_reg, dept.emp_time_in, dept.emp_time_off from employee, dept where employee.emp_id = dept.emp_id;
but how to calculate total time in table dept for emp_time_in and emp_time_off for a day and a month?
What an awful design; what made you create EMP_TIME_IN and EMP_TIME_OFF VARCHAR2 columns? Those should have been DATE ones. I suggest you change that.
Meanwhile, you'll have to concatenate EMP_REG_DATE and those IN and OFF columns in order to get DATE value; then, by subtracting two dates, you'd get number of DAYS and - using a little bit of mathematics - get hours, minutes, or whatever you want.
For example:
SQL> create table test
2 (empno number,
3 emp_reg_date date,
4 emp_time_in varchar2(10),
5 emp_time_off varchar2(10));
Table created.
SQL> insert into test values (1, date '2018-03-20', '10:00', '19:00');
1 row created.
SQL> insert into test values (1, date '2018-03-21', '11:30', '12:30');
1 row created.
SQL> insert into test values (2, date '2018-03-25', '13:00', '16:20');
1 row created.
Employee 1 worked 9 hours + 1 hour = 10 hours in total. Employee 2 worked 3 hours 20 minutes.
SQL> with dates as
2 (select
3 empno,
4 to_date(to_char(emp_reg_date, 'dd.mm.yyyy') || emp_time_in , 'dd.mm.yyyy hh24:mi') date_in,
5 to_date(to_char(emp_reg_date, 'dd.mm.yyyy') || emp_time_off, 'dd.mm.yyyy hh24:mi') date_off
6 from test
7 ),
8 summary as
9 (select empno,
10 sum(date_off - date_in) diff_days
11 from dates
12 group by empno
13 )
14 select empno,
15 trunc(diff_days * 24) hours,
16 round((diff_days * 24 - trunc(diff_days * 24)) * 60) minutes
17 from summary;
EMPNO HOURS MINUTES
---------- ---------- ----------
1 10 0
2 3 20
SQL>
Note that there's practically no control over what you enter into VARCHAR2 columns TIME_IN and TIME_OUT; what prevents you from entering AX:FM or 99:45 or A-b-e_XF in there? All those are valid strings, but invalid times.
Once again: fix data model.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments