Oracle query - Excluding records based on timestamp
Posted on 2015-02-21
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 * 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'))
(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