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
Solved

Oracle query - Excluding records based on timestamp

Posted on 2015-02-21
3
367 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
  • 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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…

856 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