Solved

Oracle query - Excluding records based on timestamp

Posted on 2015-02-21
3
362 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 142

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 142

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SyBase Query Syntax Date Time conversion 4 27
Export table into csv file in oracle 10 45
Make query more efficient 1 17
SQL Query Syntax Join 4 34
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now