Problem With SQL Query and Minutes
Posted on 2011-05-09
When I try to sum up a numeric field that contains number of hours worked (i.e. employee shift duration), it comes up like 4.75 hours. How can I convert it to like 4.75 hours is really 5 hours and 15 minutes? Please find the query below.
select convert(varchar(20), p.payroll_date,1) as payroll_date,
p.employee_no, e.employee_first_name, e.employee_last_name, e.home_store_no,
SUM(l.gross_line_amount) as hours_worked,
from av_transaction_line l
right outer join av_payroll_detail p
on p.av_transaction_id=l.av_transaction_id and l.line_id = p.line_id
inner join employee as e
on p.employee_no = e.employee_no
l.line_object in ('9059')
and p.payroll_date between '2011-01-01 00:00:00' and '2011-04-30 23:59:59'
group by convert(varchar(20), p.payroll_date,1), p.employee_no, l.line_object, p.payroll_entry_type,
e.employee_first_name, e.employee_last_name, e.home_store_no, l.line_object_type
order by convert(varchar(20), p.payroll_date,1), e.home_store_no, p.employee_no