By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,940 Members | 1,300 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,940 IT Pros & Developers. It's quick & easy.

Sum up total hours for mutiple day entries

P: 1
Can't figure out how to sum up a total of hours from same date entries.

Query:

SELECT work_hrs_for_day.empl_numbr, work_hrs_for_day.user_date, work_hrs_for_day.workday_totl, mech_hrs_for_day.day_totl

FROM work_hrs_for_day
LEFT JOIN mech_hrs_for_day

ON (work_hrs_for_day.user_date = mech_hrs_for_day.job_date) AND (work_hrs_for_day.empl_numbr = mech_hrs_for_day.empl_numbr)

WHERE work_hrs_for_day.empl_numbr= 699 AND
work_hrs_for_day.user_date >= 01/24/2008 AND
work_hrs_for_day.user_date >= 02/28/2008 AND
work_hrs_for_day.workday_totl > .5 AND
(abs(work_hrs_for_day.workday_totl - mech_hrs_for_day.day_totl) > .5 OR mech_hrs_for_day.day_totl is Null)

ORDER BY user_date


Result:

empl_number user_date workday_totl day_totl
699 1/3/2008 5.03 13.83
699 1/3/2008 8.8 13.83
699 1/4/2008 3.95 11.76
699 1/4/2008 7.81 11.76
699 1/7/2008 8.17 13.67
699 1/7/2008 5.5 13.67


Can't figure out how I can add the date totals so they only show the total of that day!
Mar 14 '08 #1
Share this Question
Share on Google+
1 Reply


Delerna
Expert 100+
P: 1,134
If I understand correctly
Expand|Select|Wrap|Line Numbers
  1. SELECT a.empl_numbr, a.user_date, 
  2.        sum(a.workday_totl) as workday_totl, 
  3.        sum(b.day_totl) as day_totl
  4. FROM work_hrs_for_day a
  5. LEFT JOIN mech_hrs_for_day b
  6. ON a.user_date = b.job_date AND a.empl_numbr = b.empl_numbr
  7. WHERE a.empl_numbr= 699 AND 
  8.       a.user_date >= 01/24/2008 AND 
  9.       a.user_date >=  02/28/2008 AND  
  10.       a.workday_totl > .5 AND 
  11.       abs(a.workday_totl -b.day_totl) > .5 OR a.day_totl is Null
  12. GROUP BY a.empl_numbr,, a.user_date
  13. ORDER BY user_date
  14.  
I added the group by clause at the end
and the sum aggregates in the select clause
I also gave the tables an alias to make the query a bit easier to read
and I wraped the query In code tags, again to make it easier to read.
You should do that for your code when asking a question.
Easy to read for us = more likely to get an answer for you
Mar 18 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.