| record event if no rows are returned between two timestamp [message #577928] |
Thu, 21 February 2013 17:45  |
 |
ruchimca1
Messages: 3
Registered: February 2013
|
Junior Member |
|
|
I have employees under a supervisor defines as below:
select LOGIN_USER_ID from APPWMS.VIEW_EMP_LATEST_INFO where SPVSR_LOGIN_USER_ID='erbrand' and EMP_STAT_CODE='ACTIVE'
Now I need to determine if all above employees are clocked in , clocked out or not clocked between yesterday and today using following:
select to_char(CLOCK_IN_DATE,'dd-mon-yyyy hh24:mi:ss' ) ClockIn,to_char(CLOCK_OUT_DATE,'dd-mon-yyyy hh24:mi:ss' ) ClockOut ,LOGIN_USER_ID,--CLOCK_IN_DATE,CLOCK_OUT_DATE, CLOCK_OUT_DATE-CLOCK_IN_DATE,trunc(sysdate) , trunc(sysdate-1),
case when CLOCK_OUT_DATE is null then
'Not clocked out'
else
case when CLOCK_OUT_DATE-CLOCK_IN_DATE is null then
'Not clocked out'
else
extract(hour from (CLOCK_OUT_DATE-CLOCK_IN_DATE) day to second) ||':'||
extract(minute from (CLOCK_OUT_DATE-CLOCK_IN_DATE) day to second) ||':'||
extract(second from (CLOCK_OUT_DATE-CLOCK_IN_DATE) day to second) ||''
end
end as TotalTime
from APPWMS.E_EMP_PERF_SMRY
where LOGIN_USER_ID in (select LOGIN_USER_ID from APPWMS.VIEW_EMP_LATEST_INFO where SPVSR_LOGIN_USER_ID='erbrand' and EMP_STAT_CODE='ACTIVE')
and (CLOCK_IN_DATE is null or CLOCK_IN_DATE between TO_DATE(TO_CHAR(sysdate -1, 'YYYY-MM-DD')|| ' 12:00:00', 'YYYY-MM-DD HH24:MI:SS')
and TO_DATE(TO_CHAR(sysdate, 'YYYY-MM-DD')|| ' 11:59:00', 'YYYY-MM-DD HH24:MI:SS'))
The first SQL gives me 66 rows while second gives me 40 rows. For 26 people , no rows are returned which means these people donot have a clock in record between two timestamps.
How can we modify query to show those 26 people as 'Not clocked In'
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: record event if no rows are returned between two timestamp [message #577968 is a reply to message #577936] |
Fri, 22 February 2013 02:41  |
cookiemonster
Messages: 9169
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
ruchimca1 wrote on Fri, 22 February 2013 03:57so the problem is:
outer join works if I am joining two tables and right table does not have a matching key.
In my case, right table has matching key (i.e LOGIN_USER_ID) for previous dates but not between two timestamps I am searching in where clause.
........
So if now I use OUTER JOIN combining both Table-1 and Table-2, Both ABC and XYZ have a matching entry in Table-2 but XYX does not have a entry if I use where clause for sysdate. How can I make sure that if I use where clause for two timestamps and a matching entry is not found , it is displayed as NULL.
You can join on more than just keys you know. Include the date range check in the outer-join, rather than having it in the where clause.
[Updated on: Fri, 22 February 2013 02:42] Report message to a moderator |
|
|
|
] 
Members
Search
Help
Register
Login
Home
