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
28
Medium Priority
?
309 Views
Last Modified: 2012-05-11
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
Comment
Question by:rvattakunnel
  • 17
  • 7
  • 3
  • +1
28 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
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

by:rvattakunnel
ID: 35721313
It is giving in numeric format.  
0
 

Author Comment

by:rvattakunnel
ID: 35721319
How can i convert it to 4 hours and 45 minutes then?
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 

Author Comment

by:rvattakunnel
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

by:Patrick Matthews
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 

Open in new window

0
 

Author Comment

by:rvattakunnel
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

by:Sharath
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

Open in new window

0
 

Author Comment

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

Author Comment

by:rvattakunnel
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

by:Patrick Matthews
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

by:rvattakunnel
ID: 35722612
Matthews,

Yours did not work.

Thanks
0
 

Author Comment

by:rvattakunnel
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

by:rvattakunnel
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

by:Sharath
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

Open in new window

0
 

Author Comment

by:rvattakunnel
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

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

Author Comment

by:rvattakunnel
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

by:rvattakunnel
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

by:Sharath
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

by:rvattakunnel
ID: 35723747
It should be 7:52
0
 
LVL 41

Expert Comment

by:Sharath
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

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
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

by:rvattakunnel
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

by:rvattakunnel
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

by:Sharath
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

Open in new window

0
 

Author Comment

by:rvattakunnel
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

by:
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

Open in new window

0
 

Author Comment

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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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ā€¦

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question