3

I get good results until I try to perform calculations on an alias column. I am aware that that is not possible. However I need a breakthrough. Can someone lend a hand please :) I have to finish this report and I know the answer to this will help me with many different scenarios as far as MYSQL Queries go. HELP

SELECT
    `ohrm_user`.`emp_number`, 
    `ohrm_attendance_record`.`punch_in_user_time`, 
     `ohrm_attendance_record`.`punch_out_user_time`, 
     `ohrm_user`.`user_name`, 
     time_to_sec(TIMEDIFF (punch_out_user_time,punch_in_user_time)) /60 AS time_to_sec_TIMEDIFF_punc, 
     SEC_TO_TIME(SUM(TIME_TO_SEC(ohrm_attendance_record.punch_out_user_time) - TIME_TO_SEC(ohrm_attendance_record.punch_in_user_time))) AS SEC_TO_TIME_SUM_TIME_TO_S, 
     TIMEDIFF(MIN(ohrm_attendance_record.punch_in_user_time),
     MAX(ohrm_attendance_record.punch_out_user_time)) AS TIMEDIFF_MIN_ohrm_attenda
FROM 
     `ohrm_attendance_record` `ohrm_attendance_record`
      INNER JOIN `ohrm_user` `ohrm_user` ON 
     (`ohrm_user`.`emp_number` = `ohrm_attendance_record`.`employee_id`)
WHERE ( `ohrm_user`.`user_name` LIKE 'mel%' )
AND ( `ohrm_attendance_record`.`punch_in_user_time` > '2015-06-29 00:00:00' )
AND ( `ohrm_attendance_record`.`punch_out_user_time` < '2015-07-01 00:00:00' )
GROUP BY `ohrm_user`.`emp_number`, 
         `ohrm_attendance_record`.`punch_in_user_time`, 
         `ohrm_attendance_record`.`punch_out_user_time`, 
         `ohrm_user`.`user_name`
ORDER BY `ohrm_user`.`user_name`, 
         `ohrm_attendance_record`.`punch_in_user_time`

I cannot figure out how to calculate total hours for the user and date scope that is specified. The way it currently is the calculations only seem to total up each punch in and out in pairs. I need a total for the hours worked in the searched period. I am still learning about aggregates, sub queries and advanced normalization.

  • Not sure I understand well enough - but you can use this query as a subquery and do another aggregation in the outer query. – jkavalik Jul 9 '15 at 7:22
1

If you group by the punch in and out times you will ensure your aggregates do not take place at the user level. Take that out and add a sum around your timediff to aggregate the time worked. Also, Username, unless it is unique and corresponds to Emp Number may cause grouping issues.

SELECT
    `ohrm_user`.`emp_number`, 
    `ohrm_attendance_record`.`punch_in_user_time`, 
     `ohrm_attendance_record`.`punch_out_user_time`, 
     `ohrm_user`.`user_name`, 
     SUM(time_to_sec(TIMEDIFF (punch_out_user_time,punch_in_user_time)) /60) AS time_to_sec_TIMEDIFF_punc, 
     SEC_TO_TIME(SUM(TIME_TO_SEC(ohrm_attendance_record.punch_out_user_time) - TIME_TO_SEC(ohrm_attendance_record.punch_in_user_time))) AS SEC_TO_TIME_SUM_TIME_TO_S, 
     TIMEDIFF(MIN(ohrm_attendance_record.punch_in_user_time),
     MAX(ohrm_attendance_record.punch_out_user_time)) AS TIMEDIFF_MIN_ohrm_attenda
FROM 
     `ohrm_attendance_record` `ohrm_attendance_record`
      INNER JOIN `ohrm_user` `ohrm_user` ON 
     (`ohrm_user`.`emp_number` = `ohrm_attendance_record`.`employee_id`)
WHERE ( `ohrm_user`.`user_name` LIKE 'mel%' )
AND ( `ohrm_attendance_record`.`punch_in_user_time` > '2015-06-29 00:00:00' )
AND ( `ohrm_attendance_record`.`punch_out_user_time` < '2015-07-01 00:00:00' )
GROUP BY `ohrm_user`.`emp_number`, 
         --REMOVE -`ohrm_attendance_record`.`punch_in_user_time`, 
         --REMOVE -`ohrm_attendance_record`.`punch_out_user_time`, 
         `ohrm_user`.`user_name`
ORDER BY `ohrm_user`.`user_name`, 
         `ohrm_attendance_record`.`punch_in_user_time`
  • Dave, This was helpful. I took your advice and enacted it in a subreport query. And this works but I have a residual issue and Im hoping you can shed light, The problem now is that SUM(time_to_sec(TIMEDIFF (punch_out_user_time,punch_in_user_time)) /60) AS time_to_sec_TIMEDIFF_punc, generates good results but my other calculations are not converting hours properly. Can you offer a suggestion on how to convert those minutes to hours and minutes in a way that will work as well as the line I just mentioned works? Im updating the info above to show you what it looks like now. – broschats Jul 12 '15 at 10:20
  • Is it an issue with your functions? SUM is your outermost aggregation for time_to_sec_TIMEDIFF_punc but you have SEC_TO_TIME outside of your SUM() for SEC_TO_TIME_SUM_TIME_TO_S. Seems like you want to SUM the difference of the two sec_to_time functions. – Dave Jul 13 '15 at 17:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.