Hi , i need your help regarding employee attendance report
i have 2 tables , one is attendance table and other one is employee table .
Employee table ( emp_id , employee_name )
attendance table ( emp_id , date_attn (time and date) , action (IN or OUT ) )
i used this qurey :
SELECT c.emp_id, c.EMP_NAME,TO_DATE(L.TRANS_DATE,'dd/mm/RRRR') attn_date , TO_DATE(L.TRANS_DATE,'dd/mm/RRRR HH:MI:SS') time
from Employee C , Attendance L
WHERE L.EMP_ID = c.EMP_ID
AND TO_DATE(l.date_attn,'dd/mm/RRRR') BETWEEN to_date(:DATE_FROM,'dd/mm/RRRR') AND to_date(:DATE_TO,'dd/mm/RRRR')
i need to get attendance for each day between two dates , i can get it by joining 2 tables , but the result is showing only the dates which employee
have attend , i want show data including date which not have data , for example
Employee |month date |attend_date |attend(IN) |attend(OUT)
JohanROM |01/02/2019 |01/02/2019 |08:00 AM |05:00PM|
JohanROM |02/02/2019 |02/02/2019 |08:15 AM |04:30PM|
JohanROM |03/02/2019 |NULL|NULL |NULL
JohanROM |04/02/2019 |02/02/2019 |NULL |04:30PM|
MARKANGA |01/02/2019 |01/02/2019 |08:00 AM |05:00PM|
MARKANGA |02/02/2019 |null 08:00 AM |05:00PM|
MARKANGA |03/02/2019 |01/02/2019 |08:00 AM |05:00PM|
my problem that i need to show even the days which he not attend with weekend also not only working day , and also include action (in , Out ) in same record .