Mac M
asked on
Hours Worked
I need a sql query that will show me how many hours worked last week
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?
--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
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?
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
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
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
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'
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
Thanks in advance.