SQL - Select statement to get all records that have a transaction date within a 24 hour period
Posted on 2011-05-06
First to set the stage --> The labor transaction table in our database is used to store the number of hours that technicians work on projects. Whenever a tech works on a project during the day, he/she enters a labor transaction into that table. That transaction documents (1) the identity of the tech who did the work, (2) the project number of the project worked on, (3) the date and time worked, and (4) the number of hours worked. One technician may work on many projects during the day so may well enter many transactions each day.
Some of our technicians work 24 hour shifts (from 6PM one day to 6PM the next day). I am attempting to write a query that will tell me how many hours a given technician has worked during that 24 hour period.
The tricky part --> if I run the query during a technician's 24 hour shift, I want the query to return transactions only for that 24 hour shift.
Example --> Shift runs from 6:00 PM on May 5th to 6:00 PM on May 6th. If I run the query at 9:00 PM on May 5th, I want the results to include only those transactions that were entered between 6:00 PM and 9:00 PM on May 5th. If I run the query at 2:00 AM on May 6th, I want the results to include only those transactions that were entered between 6:00 PM on May 5th and 2:00 AM on May 6th. If I run the query at 4:00 PM on May 6th, I want the results to include only those transactions that were entered between 6:00 PM on May 5th and 4:00 PM on May 6th.
Any ideas on how to script this? I don't want to use specific dates. I would rather use system date.
Hopefully this makes sense. If not, let me know so that I can clarify.
Also, I need solutions for both SQL Server and Oracle.
Thanks in advance.
Following is a simple script that I am running successfully but it doesn't do all of the date logic:
select sum(isnull(regularhrs,0) + isnull(premiumpayhours,0)) from labtrans
where siteid = 'BEDFORD' and startdate < GETDATE() - 1 and laborcode = 'PEDRICK'