II create this report .... the result is fine i just want to show the dates which have no records... this query Not showin apsent persons...
SELECT DISTINCT
b.emp\_name,
b.department\_name,
b.emp\_no,
b.date\_worked,
b.in\_out\_flag,
MIN(TO\_CHAR (b.time\_in, 'HH:MI:SS AM')) OVER (PARTITION BY b.emp\_no, b.date\_worked) time\_in,
MAX(TO\_CHAR (b.time\_out, 'HH:MI:SS AM')) OVER (PARTITION BY b.emp\_no, b.date\_worked) time\_out,
(SUM (b.time\_worked) OVER (PARTITION BY b.emp\_no, b.date\_worked)) \* 24 hours\_worked,
CASE WHEN (SUM (b.time\_worked) OVER (PARTITION BY b.emp\_no, b.date\_worked)) \* 24 = 0 THEN
'Absent'
WHEN (SUM (b.time\_worked) OVER (PARTITION BY b.emp\_no, b.date\_worked)) \* 24 \< 8 THEN
'Half\_Leave'
ELSE 'Present'
END alias_name
FROM (SELECT a.emp_name,
a.department\_name,
a.in\_out\_flag,
a.emp\_no,
LEAD(a.time\_stamp, 0) OVER (PARTITION BY a.emp\_no, TRUNC(a.time\_stamp, 'DD') ORDER BY a.emp\_no, a.time\_stamp) time\_in,
LEAD(a.time\_stamp, 1) OVER (PARTITION BY a.emp\_no, TRUNC(a.time\_stamp, 'DD') ORDER BY a.emp\_no, a.time\_stamp) time\_out,
(LEAD(a.time\_stamp, 1) OVER (PARTITION BY a.emp\_no, TRUNC(a.time\_stamp, 'DD') ORDER BY a.emp\_no, a.time\_stamp)
- LEAD(a.time\_stamp, 0) OVER (PARTITION BY a.emp\_no, TRUNC(a.time\_stamp, 'DD') ORDER BY a.emp\_no, a.time\_stamp)) time\_worked,
a.date\_worked
FROM (SELECT emp\_name,
department\_name,
emp\_no,
TO\_DATE(TO\_CHAR(time\_stamp, 'DD-MON-YYYY HH24:MI'), 'DD-MON-YYYY HH24:MI') time\_stamp,
in\_out\_flag,
TRUNC(time\_stamp) date\_worked
FROM emp22) a
) b
WHERE b.in_out_flag = 'IN'
AND b.date_worked BETWEEN TO_DATE('01-MAY-2010', 'DD-MON-YYYY') AND
TO\_DATE('31-MAY-2010 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
Result of this query is
I want to show Apsents if a person have no record in Table .... Check last_Line of My query ....
30 days of employees should be apsent...