Computing SUM on DATETIME datatype

hi everybody,
i'm trying to calculate the 'SUM' of time spent in hrs. n min. How can i do this using SQL Server?
What i mean is, i've a column 'TIME_SPENT' that has 'datetime' datatype. This column saves time spent for an activity in format 'hh:mm'. Suppose a user spends 45min for activity 'A' and say 1hr 25 min for activity 'B' then i want to calculate the 'SUM' of 'TIME_SPENT' for the user which should appear as 'Total time spent =2:10'

Can somebody pls help me with this?

Thnx in advance.create table #timetable (username varchar(50),timespend varchar(8))

insert into #timetable values ('joe','03:01')
insert into #timetable values ('joe','00:01')
insert into #timetable values ('foo','00:03')
insert into #timetable values ('foo','01:02')



select username,
convert(varchar(5),dateadd(second,sum(datediff(sec ond,'19000101','1900-01-01T'+timespend+':00')),'19000101'),8)
as t_timespend
from #timetable
group by username|||hey thnx for ur reply mallier,

ur code works great for the example u explained. But when i try to run it for my table in the database it gives following error:-
'The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.'

My code goes as follows:-

select empid,
convert(varchar(5),dateadd(second,sum(datediff(sec ond,'19000101','1900-01-01T'+time_spent+':00')),'19000101'),8) as 'Time spent'
from timesheet
where empid=9
group by empid
can u pls guide me on this?

thnx once again.|||try this select query on ur table (I hope data is in 'hh:mm' format)


create table #timetable (username varchar(50),timespend char(8))

insert into #timetable values ('joe','03:01')
insert into #timetable values ('joe','00:01')
insert into #timetable values ('foo','00:03')
insert into #timetable values ('foo','01:02')



select username,case when days>0 then cast(days*24+cast(left(times,2) as int)as varchar)+':'+right(times,2)
else
times
end as total_time
from
(
select username,
datediff(day,'19000101',dateadd(second,sum(datedif f(second,'19000101','1900-01-01T'+ltrim(rtrim(timespend))+':00')),'19000101'))
as days,
convert(varchar(5),dateadd(second,sum(datediff(sec ond,'19000101','1900-01-01T'+ltrim(rtrim(timespend))+':00')),'19000101'),8 ) as times
from #timetable
group by username
) as tm|||create table #babu ( names varchar(10),times varchar(10))

insert into #babu values ('babu0','01:20')
insert into #babu values ('babu1','01:20')
insert into #babu values ('babu1','01:20')
insert into #babu values ('babu0','01:20')
insert into #babu values ('babu0','01:20')

select names, dateadd(second,sum(datediff(second,'1900-01-01',convert(datetime,times))),'1900-01-01') from #babu group by names|||create table #babu ( names varchar(10),times varchar(10))

insert into #babu values ('babu0','01:20')
insert into #babu values ('babu1','01:20')
insert into #babu values ('babu1','01:20')
insert into #babu values ('babu0','01:20')
insert into #babu values ('babu0','01:20')


select names, dateadd(second,sum(datediff(second,'1900-01-01',convert(datetime,times))),'1900-01-01') from #babu group by namesthat wont help him,b'cos datatype of time column is char and he dont want see the date value too.

Computing Frequency Distributions

What's the best way to calculate frequency distributions in SQL Server 2000? What I want to do is something akin to the Excel FREQUENCY() function, which takes two arguments: a range of data, and a sorted range of "bins" that define the intervals that the input range is compared against.

I can't use GROUP BY for this, as the data I want to analyze isn't amentable to that approach.

This seems like a common thing, and searching for "Frequency Distribution" in the SQL Server docs doesn't yield any results. Nor does it look like Analysis Services does this either.

I see some commerical products that provide this capability, but I'm looking for something using the tools I am already using, namely MSDE or SQL 2k.I really should have titled this "Computing Histograms". I wound up answering my own question when I reformulated my question a bit and used the correct terminology.

If I was using Oracle I could use the handy, fancy WIDTH_BUCKET predicate to do this. Unfortunately these SQL extensions are not supported by MSSQL Server.

Instead you can do something like this:

DECLARE @.numsteps int, @.start float, @.end float
SELECT @.numsteps=50, @.start='1', @.end='51'

SELECT step, count(*) AS cnt
FROM ( SELECT floor((icud-mn) / (1.0*range/@.numsteps) ) + 1 AS step
FROM ( SELECT min(icud) AS mn, max(icud)-min(icud)+1 AS range
FROM MPARDATA
WHERE icud >= @.start AND icud < @.end
) AS R
CROSS JOIN
( SELECT * FROM MPARDATA
WHERE icud >= @.start AND icud < @.end
)
AS S)
AS RS
GROUP BY step
ORDER BY step

This will return the number of rows in my table MPARDATA where 'icud' (a positive integer value) is 1, 2, 3, 4, 5, and so on.

This article was my inspiration:

http://www.sqlmag.com/Articles/Print.cfm?ArticleID=38251

Hope others find it useful.sqlsql

computation and promutation

i am trying to set up a quick and dirty, but efficient way to calculate permutation and combination. anyone out there dealt with this?any takers?|||The method depends on what kind of permutations you want to do.

Given N objects in X different positions, do you want to allow repetitions? Do you want to include permutations with less than X values?

There are formulas available that will calculate these. Excel actually has some good statistical functions that not only do the calculations, but the help file does a good job of explaining the formula used. You might run a couple test on a spreadsheet until you are sure what kind of permutation you want, and then port the formula from Excel's help file into a TSQL statement.|||ok, it's been awhile, and i barely passed it, but knowing the total number (9999), what is the n in this combination formula:

9999 = n! / (4!(n-4)!)|||never mind, it doesn't jive, back to the drawing board|||N is the number of items, and I believe 4 is the number of locations.

N! is N Factorial, which SQL Server does not have as a standard function, but is simply 1*2*3*4*5...*N, so you could write the function easily enough.|||...and 9999 factorial would be HUGE! We're way beyond "grains of sand" and into "atoms in the universe" here...