Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# Problem With SQL Query and Minutes

Posted on 2011-05-09
Medium Priority
309 Views
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.
Thanks,

Raj

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,
p.payroll_entry_type,
l.line_object, l.line_object_type
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
where
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
0
Question by:rvattakunnel
• 17
• 7
• 3
• +1

LVL 93

Expert Comment

ID: 35721299
rvattakunnel,

Please explain.  If gross_line_amount is already giving the decimal hours, then 4.75 would really be 4:45, not 5:15.

And if gross_line_amount is not giving decimal hours, then you have a big problem.

Patrick
0

Author Comment

ID: 35721313
It is giving in numeric format.
0

Author Comment

ID: 35721319
How can i convert it to 4 hours and 45 minutes then?
0

Author Comment

ID: 35721328
I have records that are like 4.66, 5.82.  I would like to extract the data and feed into our ERP GL system.
0

LVL 93

Expert Comment

ID: 35721354
Try:

``````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,
p.payroll_entry_type,
l.line_object, l.line_object_type
/* new columns start here! */
, CAST(FLOOR(SUM(l.gross_line_amount)) AS int) AS whole_hours,
CAST(FLOOR(SUM(l.gross_line_amount) - FLOOR(SUM(l.gross_line_amount))) AS int) AS minutes
/* new columns stop here! */
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
where
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
``````
0

Author Comment

ID: 35721390
That didn't qute work.  I am getting 1 hour for whole hours, 1 minute for minute where the hours worked was 1.92.
0

LVL 41

Expert Comment

ID: 35721886
Can you check this.
``````select payroll_date,employee_no,employee_first_name,employee_last_name,home_store_no,
convert(varchar,convert(int,hours_worked))+':'+convert(varchar,convert(int,(hours_worked-CONVERT(int,hours_worked))*60)),
payroll_entry_type,line_object,line_object_type
from (
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,
p.payroll_entry_type,
l.line_object, l.line_object_type
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
where
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) t1
order by payroll_date, home_store_no, employee_no
``````
0

Author Comment

ID: 35722239
That worked out well.  But I have off by about a minute on some records.  Any ideas?
0

Author Comment

ID: 35722270
Also I get 6:0 instead of 6:00.  I tried to change up the character type on the query but I didn't get the results that I wanted.  I wasn't sure I had the right data type either.
0

LVL 93

Expert Comment

ID: 35722460
Sorry, mine shoulf have been:

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,
p.payroll_entry_type,
l.line_object, l.line_object_type
/* new columns start here! */
, CAST(FLOOR(SUM(l.gross_line_amount)) AS int) AS whole_hours,
60 * CAST(SUM(l.gross_line_amount) - FLOOR(SUM(l.gross_line_amount)) AS int) AS minutes
/* new columns stop here! */
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
where
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
0

Author Comment

ID: 35722612
Matthews,

Yours did not work.

Thanks
0

Author Comment

ID: 35722698
Sharath,

I think I spoke too soon...   Check out the column that has 8:7

02/04/11      89856      KEITH      S      121      8:7      20      9059      13
02/05/11      87538      STEVEN      A      102      5:34      10      9059      13
02/05/11      87978      MARY      HI      102      9:42      10      9059      13
02/05/11      88165      GENEVIE      G      102      4:0      10      9059      13
02/05/11      88568      SHIRLEY      H      102      6:38      10      9059      13
02/05/11      88377      ERIC      A      102      14:42      10      9059      13
02/05/11      89688      ANGEL      G      102      10:57      20      9059      13
02/05/11      88473      RAUL      A      103      4:10      20      9059      13
0

Author Comment

ID: 35722784
These records are supposed to be like 9:06, 9:01, 9:07, 9.01:

9:6
9:1
9:7
9:1
8:36
0

LVL 41

Expert Comment

ID: 35723155
You are right. check this.
``````select payroll_date,employee_no,employee_first_name,employee_last_name,home_store_no,
convert(varchar,convert(int,hours_worked))+':'+right('0'+convert(varchar,convert(int,(hours_worked-CONVERT(int,hours_worked))*60)),2),
payroll_entry_type,line_object,line_object_type
from (
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,
p.payroll_entry_type,
l.line_object, l.line_object_type
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
where
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) t1
order by payroll_date, home_store_no, employe
``````
0

Author Comment

ID: 35723430
Sharath,

Almost.  It is still off by one minute or certain records?  Is it related to the data type?

Thanks,

Rajesh
0

LVL 41

Expert Comment

ID: 35723450
>> It is still off by one minute or certain records?
Could you post such sample data?
0

Author Comment

ID: 35723554
01/30/11      1161      TERESA      H      104      6:13      10      9059      13
01/31/11      1161      TERESA      H      104      8:10      10      9059      13
02/03/11      1161      TERESA      H      104      8:31      10      9059      13
02/04/11      1161      TERESA      H      104      7:45      10      9059      13
02/05/11      1161      TERESA      H      104      7:51      10      9059      13

Think 7:51 should be 7:52 (5.23 + 2.29)
Think 8:31 should be 8:32 (2.51 + 5:41)
0

Author Comment

ID: 35723585
Sample data from the dbase. I took out the sum in the query statement

01/30/11      1161      TERESA      H      104      4.2500      10      9059      13
01/30/11      1161      TERESA      H      104      1.9700      10      9059      13
01/31/11      1161      TERESA      H      104      4.7500      10      9059      13
01/31/11      1161      TERESA      H      104      3.4200      10      9059      13
02/03/11      1161      TERESA      H      104      5.6800      10      9059      13
02/03/11      1161      TERESA      H      104      2.8500      10      9059      13
02/04/11      1161      TERESA      H      104      5.2500      10      9059      13
02/04/11      1161      TERESA      H      104      2.5000      10      9059      13
02/05/11      1161      TERESA      H      104      5.3800      10      9059      13
02/05/11      1161      TERESA      H      104      2.4800      10      9059      13
0

LVL 41

Expert Comment

ID: 35723730
Decimal Value => HH:MM
5.38                   5:22
2.48                   2:28
7.86 (5.38+2.48) 7:51 (5:22 + 2:28 = 7:50)
Do you want 7:51 or 7:50 for 7.86? Instead of converting to int, which is floor, I think you need ceiling value.
0

Author Comment

ID: 35723747
It should be 7:52
0

LVL 41

Expert Comment

ID: 35724616
Can you check this?
``````select payroll_date,employee_no,employee_first_name,employee_last_name,home_store_no,
convert(varchar,convert(int,hours_worked))+':'+right('0'+convert(varchar,convert(int,round((hours_worked-CONVERT(int,hours_worked))*60,0))),2),
payroll_entry_type,line_object,line_object_type
from (
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,
p.payroll_entry_type,
l.line_object, l.line_object_type
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
where
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) t1
order by payroll_date, home_store_no, employe
``````
0

LVL 75

Expert Comment

ID: 35726291
>>Yours did not work.<<
If you want to get help on this site, you would be wise to read up on the EE Guidelines.  Stating "did not work" is meaningless.  What did not work?
1. Was there an error message if so what was it?
2. Did it produce the wrong output, if so what was it and what was the desired output?
3. Something else?

0

Author Comment

ID: 35728337
I have run your query and it looks like it took care of the minute difference on those couple of records.  I will do some more further testing before I give the final word, please.  Thanks,

Rajesh
0

Author Comment

ID: 35746456
Sharath,

I spoke too soon. It looks there is still one minute difference in the data.

select payroll_date,employee_no,employee_first_name,employee_last_name,home_store_no,
convert(varchar,convert(int,hours_worked))+':'+right('0'+convert(varchar,convert(int,(hours_worked-CONVERT(int,hours_worked))*60)),2) as hours_worked_per_day,
payroll_entry_type as 'type_of_hours',line_object,line_object_type
from (
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,
p.payroll_entry_type ,
l.line_object, l.line_object_type
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
where
l.line_object in ('9059')
and p.employee_no='88130'
and p.payroll_date between '2011-04-18 00:00:00' and '2011-04-18 23:59:59'
and e.home_store_no='107'
group by convert(varchar(20), p.payroll_date,1), p.employee_no, p.payroll_entry_type,l.line_object,
e.employee_first_name, e.employee_last_name, e.home_store_no, l.line_object_type) t1
order by cast(payroll_date as datetime), home_store_no, employee_no

For example, I get 4:46 but in reality it should be 4:47.  It doesn't happen on all records but only on certain records.  I am pretty sure it has to do with rounding...

Thanks,

Rajesh
0

LVL 41

Expert Comment

ID: 35750656
This is what I am getting. Is it not correct for your requirement?
``````Decimal Value => HH:MM
4.75	=> 4.45
4.76	=> 4.46
4.77	=> 4.46
4.78	=> 4.47
4.79	=> 4.47
4.80	=> 4.48
``````
0

Author Comment

ID: 35753742
This is the current output from the query.
4.7800      4:46
3.7200      3:43
If we were to compare your previous comment and this output, 4.78 shouold 4.47 but the query is returning 4.46.
0

LVL 41

Accepted Solution

Sharath earned 2000 total points
ID: 35757384
Are you sure? I tested the same code and got the correct result.
``````declare @hours_worked decimal(10,2)
set @hours_worked = 4.78
select @hours_worked,convert(varchar,convert(int,@hours_worked))+':'+
right('0'+convert(varchar,convert(int,round((@hours_worked-CONVERT(int,@hours_worked))*60,0))),2) as hours_worked_per_day
-- 4:47
set @hours_worked = 3.72
select @hours_worked,convert(varchar,convert(int,@hours_worked))+':'+
right('0'+convert(varchar,convert(int,round((@hours_worked-CONVERT(int,@hours_worked))*60,0))),2) as hours_worked_per_day
-- 3:43
``````
0

Author Comment

ID: 35786093
Let me try again today and I will keep you posted.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased riskâ€¦
###### Suggested Courses
Course of the Month13 days, 13 hours left to enroll