?
Solved

Oracle query - Excluding records based on timestamp

Posted on 2015-02-21
3
Medium Priority
?
382 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 2000 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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

752 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