Solved

Oracle query - Excluding records based on timestamp

Posted on 2015-02-21
3
374 Views
Last Modified: 2015-02-21
Folks,

having one of those mental blocks... I have a six bundy clocks sync-ing their data to an XE database, I then generate a CSV from these to upload into our payroll.

Come Monday we don't want to upload records from two clocks (but we want to keep the clockings in the database). We also want to specify for these two clocks that we specifically don't want "IN" events after Sunday midnight, or "OUT" events after Monday after 6:30am - ie we still want records for the two clocks prior to these two times to complete a normal pay period.

the table looks like this:

     USERID        VARCHAR2(20)              -- who clocked in
     TIMEENTRY         TIMESTAMP(6)     -- time of clocking
     EVENTID        VARCHAR2(20)              -- IN/OUT indication
     TERMINALSN         VARCHAR2(20)     -- clock serial number

The query I thought would do the trick is below, running it today I get the total number of records in the table - as I would expect, but I dropped the date criteria back two days (pretending to be Sunday) and the query promptly brings nothing at all back...

Select
    count(*)
from
   actatek_logs
WHERE

NOT EXISTS
(Select * from actatek_logs WHERE

  (terminalsn IN ('00111DA023D2','00111DA01E34') AND
(
  (eventid = 'IN' AND timeentry > to_timestamp('2015-02-22 23:59', 'YYYY-MM-DD HH24:MI'))
OR
  (eventid = 'OUT' AND timeentry > to_timestamp('2015-02-23 06:30', 'YYYY-MM-DD HH24:MI'))
)
)
)

I'm guessing it is a "timestamp" thing, but any pointers would be appreciated !!

thanks in advance
0
Comment
Question by:wspall
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40622762
your NOT EXISTS is not linked to the outer query
hence as soon as the subquery finds 1 record the end result will be 0
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 40622763
your NOT EXISTS is not linked to the outer query
hence as soon as the subquery finds 1 record the end result will be 0
0
 

Author Comment

by:wspall
ID: 40622861
Thanks Guy, that was a mentl blank...

this table doesn't have a unquie record ID so created one from the two most important fields and "linked" on that

Select
    count(*)
from
   actatek_logs AL
WHERE

NOT EXISTS
(Select * from actatek_logs AL2 WHERE

AL2.userID||to_char(AL2.timeentry,'YYYY-MM-DD HH24:MI') = AL.userID||to_char(AL.timeentry,'YYYY-MM-DD HH24:MI')

AND
  (terminalsn IN ('00111DA023D2','00111DA01E34') AND
(
  (eventid = 'IN' AND to_char(timeentry,'YYYY-MM-DD HH24:MI') > '2015-02-22 23:59')
OR
  (eventid = 'OUT' AND to_char(timeentry,'YYYY-MM-DD HH24:MI') > '2015-02-23 06:30')
)
)
)

think that's done it !!
0

Featured Post

Monthly Recap

May was a big month for new releases from Linux Academy! Take a look at what our team built recently in our blog. You can access the newest releases from our blog.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question