Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL - Select statement to get all records that have a transaction date within a 24 hour period

Posted on 2011-05-06
8
Medium Priority
?
554 Views
Last Modified: 2012-05-11
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'

0
Comment
Question by:cbridgman
  • 3
  • 3
  • 2
8 Comments
 
LVL 61

Accepted Solution

by:
Kevin Cross earned 1000 total points
ID: 35710237
Do you have a table that has the shifts for a given employee?
Or are all the employees in this table working 6:00PM - 6:00PM?

For T-SQL, this will get you to 6:00PM of the previous day or current day depending on what time of the day it is:
DATEADD(HH, 18, DATEDIFF(DD, 0, GETDATE()-CASE WHEN DATEPART(HH, GETDATE()) < 18 THEN 1 ELSE 0 END))

Open in new window


If you use that in your query, you can basically get any STARTDATE >= {value above}.  You can construct something similar for the end date.
DATEADD(HH, 18, DATEDIFF(DD, 0, GETDATE()+CASE WHEN DATEPART(HH, GETDATE()) >= 18 THEN 1 ELSE 0 END))

Open in new window


In SQL 2008, you eliminate the DATEDIFF piece which is just being used to get to midnight of specific day and just use DATE().

Kevin
0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 35710249
For Oracle, you can use TRUNC() to remove the time portion of the date and follow the same concept as above which is:
+Determine if current time is after 6PM and if so subtract 1 day
+Take date (at midnight) and add 18 hours to it
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 35710259
This solution is for Oracle.  Basically the filter for startdate will be:

start_date between between
  (select CASE
     WHEN to_char(sysdate,'HH24') => 18 THEN TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD')||' 18', 'YYYY-MM-DD HH24')
     ELSE TO_DATE(TO_CHAR(SYSDATE-1,'YYYY-MM-DD')||' 18', 'YYYY-MM-DD HH24')
     END start_date
   FROM DUAL)
and sysdate;

The sub-select checks for the current hour, and if it is greater than or equal to 18 (6pm) then use 6 pm of the current day.  Else use 6 pm of the previous day.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 24

Assisted Solution

by:johanntagle
johanntagle earned 1000 total points
ID: 35710276
sorry typos.  correction:

startdate between
  (select CASE
     WHEN to_char(sysdate,'HH24') => 18 THEN TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD')||' 18', 'YYYY-MM-DD HH24')
     ELSE TO_DATE(TO_CHAR(SYSDATE-1,'YYYY-MM-DD')||' 18', 'YYYY-MM-DD HH24')
     END
   FROM DUAL)
and sysdate;
0
 

Author Comment

by:cbridgman
ID: 35710293
Thanks Kevin,

I will play around with the above and see what happens. I knew that I was going to need a case statement of some kind. I just didn't know how to put it together.

With regard to your question, employees in this table may work on any shift.

Our technicians must report no less than a certain number of hours worked every day. We have provided each of them with a personal query that they can run during their shift so that they know what they have recorded up to the point in time that they run the query. So in effect, only those technicians on the 6pm to 6pm shift will ever have to run the above. All of the other techs work on shifts with start and end times that occur on the same day.
0
 

Author Comment

by:cbridgman
ID: 35710304
thanks johann,

i will try your solution too and let you know how it goes or if I need more help
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 35710352
You're welcome.  Sorry, typo again.  It should be ">=" not "=>".  I only tested the query using ">" then realized I should add the "=", but placed it wrong.
0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 35710582
Glad we could help!

Best regards and happy coding,
Kevin
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
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…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to recover a database from a user managed backup

581 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