Link to home
Start Free TrialLog in
Avatar of Mac M
Mac MFlag for United States of America

asked on

Hours Worked

I need a sql query that will show me how many hours worked last week
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Ok, I'll bite.  Tell us what we would use to figure that out, as experts here cannot connect to your data source and run queries, so we have no basis for answering your question.
btw I see you joined EE today, so Welcome.
Typically you would the DATEDIFF func to get the amount of time between two specific datetimes, such as for each work shift, then sum then up to get the total time.

--SUM(
DATEDIFF(MINUTE, start_time, end_time) / 60.0 AS hours_worked
--)?

--NOTE: Be sure to use DATEDIFF(MINUTE and divide by 60, do not use DATEDIFF(HOUR.

It's easy to limit the query to "last week" by comparing the work datetime to a value based on the current date time, but I would need more details to give you specific code:

1) On what day does you work week start?
2) When you run the code, do you always want to see the previous week?  Only?
assuming you had a start and an end time

it would be
sum(cast(datediff(s, startTime,Endtime) as float) / 3600)
from <table>
where startTime >= @startDate --beginning of the week
and starttime <= @endDate --end of week

Open in new window


The question becomes what happens when you have overlapping data.  EG:

Start of week is considered monday at 8:00 AM.  What happens if you have someone working from 6:00 AM on?
What Jim is saying is, what does your data look like?

What is the structure of your table (table name, field names, field types)?

It would be helpful if you could provide some sample data and show us what you expect the result to look like for that data.

Dale
Avatar of Mac M

ASKER

I've requested that this question be deleted for the following reason:

This is new to me so excuse me but I'm willing and eager to learn and apparently I'm working in a Postregsql environment which I need to be able to retrieve 'how many how works last week from the Sales_Shift tbl'
ASKER CERTIFIED SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
AMTECHSRUS - As much as I appreciate the accept, that comment cannot possibly have answered your question, so please unaccept and provide us some background info necessary to answer your question.  

Thanks in advance.