October 4, 2005 at 1:17 am
I have a decimal field for store the hours and minutes 3,30 or 4,15
I want to do some calculation like total number of hours and minutes .. if I used sum I get a wrong result cause the scale calculated to the 100 not 60, I would like to use MOD but it's not available with MS SQL even Int L
What is the best way to make such calculation ?
Regards
October 4, 2005 at 2:59 am
This isn't very elegant but it works. You'll notice that the calculation of @totalmins at the end relies on integer division - related to the MOD function that you are after.
declare @t1 decimal(9,2)
declare @t2 decimal(9,2)
declare @totalsecs int
declare @totalmins int
declare @seconds int
set @t1 = 3.30
set @t2 = 4.45
set @totalsecs = (floor(@t1) + floor(@t2)) * 60 + ((@t1 - floor(@t1)) + (@t2 - floor(@t2))) * 100
set @totalmins = @totalsecs / 60
set @seconds = @totalsecs - (@totalmins * 60)
select @totalsecs TotalSeconds, @totalmins Minutes, @seconds Seconds
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 4, 2005 at 5:12 am
Thanks Phil Parkin
What I am after is achieving that calculation from SQL command, I don’t want to write a program(Stored procedure or function) I want to submit a SQL statement to get the total overtime hours:minutes for each employee .
Regards
October 4, 2005 at 5:38 am
if you change Phil's variables with your table fildes i think you get the right resuls
for example
create table dt (hhmm decimal(4,2)
insert into dt values (3.30)
insert into dt values (4.45)
select rtrim(cast(cast((sum(floor(hhmm))* 60 + sum(hhmm - floor(hhmm)) * 100) / 60 as integer) as char(2))) + ':' +
cast(sum(floor(hhmm)) * 60 + (sum(hhmm - floor(hhmm)) ) * 100 - cast(cast((sum(floor(hhmm))* 60 + sum(hhmm - floor(hhmm)) * 100) / 60 as integer) as char(2)) * 60 as char(2))
from dt
October 4, 2005 at 6:14 am
not sure this could be another solution?
select cast( sum( cast(left(cast(Hours as varchar(10)) , patindex( '%.%', cast(Hours as varchar(10)) )-1) as Int ) + cast(right(cast(Hours as varchar(10)) , 2) as Int )/60 ) as varchar(10)) + '.' +
case
when len ( cast(sum(cast(right(cast(Hours as varchar(10)) , 2) as Int ))%60 as varchar(2))) = 1 then '0' + cast(sum(cast(right(cast(Hours as varchar(10)) , 2) as Int ))%60 as varchar(2))
else cast(sum(cast(right(cast(Hours as varchar(10)) , 2) as Int ))%60 as varchar(2))
end
from Hours
Regards
Rajesh
October 4, 2005 at 6:45 am
Check this out.
select cast( sum( cast( left(cast(Hours as varchar(10)) , patindex( '%.%', cast(Hours as varchar(10)))-1) as int) )+ sum( cast( right( cast(Hours as varchar(10)),2) as int))/60 as varchar(10))+ '.' +
case
when len ( cast(sum(cast(right(cast(Hours as varchar(10)) , 2) as Int ))%60 as varchar(2))) = 1 then '0' + cast(sum(cast(right(cast(Hours as varchar(10)) , 2) as Int ))%60 as varchar(2))
else cast(sum(cast(right(cast(Hours as varchar(10)) , 2) as Int ))%60 as varchar(2))
end
from Hours
Rajesh
October 4, 2005 at 7:17 am
DATEADD(minute,SUM(DATEDIFF(minute,0,CAST(REPLACE(CAST([timecol] as varchar),'.',':') as smalldatetime))),0)
This will give you datetime value which you can then process, cast or convert as necessary
Far away is close at hand in the images of elsewhere.
Anon.
October 4, 2005 at 7:44 am
Just for clarity; this is modulo in SQL %
I wasn't born stupid - I had to study.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply