Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Help need to add Mins

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-01-12 : 08:46:46
balu writes "Hi,

I have the following table format

create table Dt(id int,Timeinmins nvarchar)

have data like

id Timeinmins
1 1.20
2 1.40
1 2.30
1 0.10
3 0.3

i want the ouput to be converted to the total number of minutes
each id is having:

1 4.00
2 1.40
3 0.3

But actually when i used the sum and group that data i am getting it as

1 3.60
2 1.40
3 0.3

Bcoz it was taking the sum means 100.But we need to calculate it for 60 mins for 1 minute....

So i need the query of the above output....
Thanks and regards
Balu"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-01-12 : 09:06:20
I don't understand your question or your data (why are you using nvarchar to store minutes ??) but some simple math lets you convert minutes to hours:

Let TotalMinutes = the total number of minutes you have added up.

Then: Total Hours = TotalMinutes / 60

And: Remaining Minutes = TotalMinutes % 60 (% is the MOD operator; see BOL for info.)

Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-12 : 20:10:07
Balu, see explaination in codes
create table #dt
(
id int,
timeinmins nvarchar(10) -- Actually your time is not in minutes but <minutes>.<seconds>
)

insert into #dt
select 1, '1.20' union all
select 2, '1.40' union all
select 1, '2.30' union all
select 1, '0.10' union all
select 3, '0.3'

select id, cast((tm_secs / 60) as int) as tm_min, tm_secs % 60 as tm_sec,
convert(varchar(2), cast((tm_secs / 60) as int)) + '.' + convert(varchar(2), tm_secs % 60) as tm_in_string
from
(
-- convert from <minutes>.<seconds> to <total seconds>
select id, cast(sum((cast(tm_num as int) * 60) + ((tm_num - cast(tm_num as int)) * 100)) as int) as tm_secs
from
(
-- convert the timeinmins from nvarchar to numeric(6,2) for ease of processing
select id, cast(timeinmins as numeric(6,2)) as tm_num
from #dt
) as dt_num
group by id
) as dt_in_secs

drop table #dt


-----------------
'KH'

Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-13 : 02:08:21
If you use DateTime datatype and store time with dates, you can use DateDiff function to get time difference where you can avoid so much conversions

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -