Hi,
I have below table. I want to take monthly report by passing from date and to date, usually it is beginning and end of the month. In attendance table, no data available during week-end and public holiday. When I use below query, it is retrieving records without considering LEFT JOIN table.
CREATE TABLE ATTENDANCE
(
EMPLOYEE_NUMBER VARCHAR2(10 BYTE),
WEEK_START_DATE DATE,
WEEK_END_DATE DATE,
TIMECARD_DATE DATE,
TIME_IN VARCHAR2(5 BYTE),
TIME_OUT VARCHAR2(5 BYTE)
)
insert into attendance values ('1001','03-DEC-2017','09-DEC-2017','03-DEC-2017','07:10','09:00')
insert into attendance values ('1001','03-DEC-2017','09-DEC-2017','03-DEC-2017','10:10','18:00')
insert into attendance values ('1001','03-DEC-2017','09-DEC-2017','04-DEC-2017','07:10','18:00')
insert into attendance values ('1001','03-DEC-2017','09-DEC-2017','05-DEC-2017','07:10','18:00')
insert into attendance values ('1001','03-DEC-2017','09-DEC-2017','06-DEC-2017','07:10','18:00')
insert into attendance values ('1001','03-DEC-2017','09-DEC-2017','07-DEC-2017','07:10','18:00')
insert into attendance values ('1001','03-DEC-2017','09-DEC-2017','10-DEC-2017','07:10','18:00')
insert into attendance values ('1001','03-DEC-2017','09-DEC-2017','11-DEC-2017','07:10','18:00')
insert into attendance values ('1002','03-DEC-2017','09-DEC-2017','03-DEC-2017','07:10','18:00')
insert into attendance values ('1002','03-DEC-2017','09-DEC-2017','04-DEC-2017','07:10','09:30')
insert into attendance values ('1002','03-DEC-2017','09-DEC-2017','04-DEC-2017','09:50','18:00')
insert into attendance values ('1002','03-DEC-2017','09-DEC-2017','05-DEC-2017','07:10','18:00')
insert into attendance values ('1002','03-DEC-2017','09-DEC-2017','06-DEC-2017','07:10','18:00')
insert into attendance values ('1002','03-DEC-2017','09-DEC-2017','07-DEC-2017','07:10','18:00')
insert into attendance values ('1002','03-DEC-2017','09-DEC-2017','10-DEC-2017','07:10','18:00')
insert into attendance values ('1002','03-DEC-2017','09-DEC-2017','11-DEC-2017','07:10','18:00')
EXPECTING OUTPUT
=================
EMPLOYEE_NUMBER DATE _TIME IN TIMEOUT
1001 01-DEC-2017
1001 02-DEC-2017
1001 03-DEC-2017 07:10 18:00
1001 04-DEC-2017 07:10 18:00
and so on upto 31-DEC-2017
Also for another employee 1002 same output if we pass two employee numbers.
But, What I am getting is, DATES AVAILABLE IN BOTH TABLES. Here I am not getting 01-DEC-2017 AND 02-DEC-2017 and all dates if no punching records exists in ATTENDANCE table.
My query is below:
===============
select d.date_Seq
,e.full_name
,e.START_TIME
,e.stop_time
from
(
select to_date(:P_FROM_DATE,'DD-MM-YYYY') + rownum -1 as date_seq from dual
connect by rownum <= trunc (to_date(:P_TO_DATE,'DD-MM-YYYY')) - to_date (:P_FROM_DATE,'DD-MM-YYYY') +1 ) d, /* To retrieve all days in a month starting from 01-DEC-2017 to 31-DEC-2017 */
( select
a.employee_number
,a.TIMECARD_DATE tcdate
,TO_CHAR(a.TIMECARD_DATE,'DAY') CDAY
,MIN(a.TIME_IN) AS start_time
,MAX(a.TIME_OUT) AS stop_Time
from
ATTENDANCE a
where 1=1
and a.TIMECARD_DATE >= to_date(:P_FROM_DATE,'DD-MM-YYYY') and a.TIMECARD_DATE <= to_date(:P_TO_DATE,'DD-MM-YYYY')
group by a.employee_number, a.timecard_Date
) e
where 1=1
AND trunc(Date_seq) = trunc(e.tcdate (+))
and e.employee_number in ('1001','1002')
order by d.date_Seq
Please correct my query.
Thanks in advance.