Home » SQL & PL/SQL » SQL & PL/SQL » record event if no rows are returned between two timestamp
record event if no rows are returned between two timestamp [message #577928] Thu, 21 February 2013 17:45 Go to next message
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 #577930 is a reply to message #577928] Thu, 21 February 2013 18:39 Go to previous messageGo to next message
BlackSwan
Messages: 22470
Registered: January 2009
Senior Member
since we don't have your table or data, we can run, test, or improve post SQL.

>The first SQL gives me 66 rows while second gives me 40 rows.
I only see a single SELECT statement.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: record event if no rows are returned between two timestamp [message #577932 is a reply to message #577930] Thu, 21 February 2013 19:26 Go to previous messageGo to next message
ruchimca1
Messages: 3
Registered: February 2013
Junior Member
second select statement starts with

select to_char(CLOCK_IN_DATE,'dd-mon-yyyy hh24:mi:ss' ...
Re: record event if no rows are returned between two timestamp [message #577933 is a reply to message #577932] Thu, 21 February 2013 19:38 Go to previous messageGo to next message
BlackSwan
Messages: 22470
Registered: January 2009
Senior Member
>How can we modify query to show those 26 people as 'Not clocked In'
use OUTER JOIN
Re: record event if no rows are returned between two timestamp [message #577936 is a reply to message #577933] Thu, 21 February 2013 21:57 Go to previous messageGo to next message
ruchimca1
Messages: 3
Registered: February 2013
Junior Member
so 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.

Example
Table-1

LOGIN_USER_ID
ABC
XYZ

Table-2
LOGIN_USER_ID CLOCK_IN CLOCK_OUT
ABC 2/15/2013 2/16/2013
XYZ 2/15/2013 2/16/2013
ABC 2/21/2013 2/21/2013

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.

My query is:

SELECT
LOGIN_USER_ID, CLOCK_IN, CLOCK_OUT
FROM
table-1
OUTER JOIN
table-2
ON
table-1.LOGIN_USERID=table-2.LOGIN_USER_ID
WHERE
CLOCK_IN BETWEEN SYSDATE-1 AND SYSDATE

THis returns only one row
ABC 2/21/2013 2/21/2013

I want this output to be returned:
ABC 2/21/2013 2/21/2013
XYZ NULL NULL
Re: record event if no rows are returned between two timestamp [message #577937 is a reply to message #577936] Thu, 21 February 2013 22:02 Go to previous messageGo to next message
BlackSwan
Messages: 22470
Registered: January 2009
Senior Member
http://www.oracle.com/pls/db112/search?remark=quick_search&word=outer+join&partno=

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: record event if no rows are returned between two timestamp [message #577949 is a reply to message #577928] Fri, 22 February 2013 01:06 Go to previous messageGo to next message
Michel Cadot
Messages: 58481
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel
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 Go to previous message
cookiemonster
Messages: 10841
Registered: September 2008
Location: Rainy Manchester
Senior Member
ruchimca1 wrote on Fri, 22 February 2013 03:57
so 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

Previous Topic: How to use default value
Next Topic: Inserting data using DBLINK
Goto Forum:
  


Current Time: Tue Jul 22 06:50:40 CDT 2014

Total time taken to generate the page: 0.13503 seconds