Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Oracle query - Excluding records based on timestamp

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

604 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