This can either be a C# or Sql question. I have a SQL table full of data and
I need to do a calculation on data that looks like this

00:00:06
00:00:36
00:00:42
00:01:48

my select statement will look something like this

SELECT *
FROM [Microcall_Analysis].[dbo].[tbl_CallData]
where extension = '6355'
and call_date between '08/20/07' and '09/20/07'

the data I listed above will be call_duration. I'm not the best sql
developer so how do I sum all of the call_duration results? Common sense
says I can't use var/char as a datatype, can I sum a DateTime type? Can I
use a varchar and use a Sum and Convert together?

Is there a way to return the results in a Dataset/Datatable and do all this
stuff in the C# code although resources will be killed? Any advice on this
one would be greatly appreciated.

Thank you for any advice.

Kevin

--
Kevin C. Brown
Developer

"KevinB" <(E-Mail Removed)> wrote in message
news:52A0BF07-96DE-468C-BCEC-(E-Mail Removed)...
> This can either be a C# or Sql question. I have a SQL table full of data
> and
> I need to do a calculation on data that looks like this
>
> 00:00:06
> 00:00:36
> 00:00:42
> 00:01:48
>
> my select statement will look something like this
>
> SELECT *
> FROM [Microcall_Analysis].[dbo].[tbl_CallData]
> where extension = '6355'
> and call_date between '08/20/07' and '09/20/07'
>
> the data I listed above will be call_duration. I'm not the best sql
> developer so how do I sum all of the call_duration results? Common sense
> says I can't use var/char as a datatype, can I sum a DateTime type? Can I
> use a varchar and use a Sum and Convert together?
>
> Is there a way to return the results in a Dataset/Datatable and do all
> this
> stuff in the C# code although resources will be killed? Any advice on this
> one would be greatly appreciated.
>
> Thank you for any advice.
>
> Kevin
>
> --
> Kevin C. Brown
> Developer

Are those all the columns in the table?? What happens if a call starts on
one day and ends the next??

LS

That query I included returns 1327 results, the call_duration is just one
column. The actual query is in a stored procedure and would look like this.
I need to sum call_duration and call_time but I'm not sure how to get those
totals. Thanks again.

SELECT [id]
,[pattern]
,[call_date]
,[call_time]
,[call_duration]
,[extension]
,[trunk]
,[dialed_number]
,[place_called]
,[cost]
,[caller_name]
FROM [Microcall_Analysis].[dbo].[tbl_CallData]
where extension = '6355'
and call_date between '08/20/07' and '09/20/07'



--
Kevin C. Brown
Developer


"Lloyd Sheen" wrote:

>
> "KevinB" <(E-Mail Removed)> wrote in message
> news:52A0BF07-96DE-468C-BCEC-(E-Mail Removed)...
> > This can either be a C# or Sql question. I have a SQL table full of data
> > and
> > I need to do a calculation on data that looks like this
> >
> > 00:00:06
> > 00:00:36
> > 00:00:42
> > 00:01:48
> >
> > my select statement will look something like this
> >
> > SELECT *
> > FROM [Microcall_Analysis].[dbo].[tbl_CallData]
> > where extension = '6355'
> > and call_date between '08/20/07' and '09/20/07'
> >
> > the data I listed above will be call_duration. I'm not the best sql
> > developer so how do I sum all of the call_duration results? Common sense
> > says I can't use var/char as a datatype, can I sum a DateTime type? Can I
> > use a varchar and use a Sum and Convert together?
> >
> > Is there a way to return the results in a Dataset/Datatable and do all
> > this
> > stuff in the C# code although resources will be killed? Any advice on this
> > one would be greatly appreciated.
> >
> > Thank you for any advice.
> >
> > Kevin
> >
> > --
> > Kevin C. Brown
> > Developer
>
> Are those all the columns in the table?? What happens if a call starts on
> one day and ends the next??
>
> LS
>
>

First, create a small sample that demo's what you want.

Here is a sample to get you started.

set nocount on

declare @holder table ( Column1 datetime )

insert into @holder (Column1) values ('00:00:06')

insert into @holder (Column1) values ('00:00:36')

insert into @holder (Column1) values ('00:00:42')

insert into @holder (Column1) values ('00:01:48')

select sum(Column1) as MySum from @holder

select avg(Column1) as MySum from @holder


then post you're expecting results.

DDL (data defintion language) is usually needed in these cases.




"KevinB" <(E-Mail Removed)> wrote in message
news:52A0BF07-96DE-468C-BCEC-(E-Mail Removed)...
> This can either be a C# or Sql question. I have a SQL table full of data
> and
> I need to do a calculation on data that looks like this
>
> 00:00:06
> 00:00:36
> 00:00:42
> 00:01:48
>
> my select statement will look something like this
>
> SELECT *
> FROM [Microcall_Analysis].[dbo].[tbl_CallData]
> where extension = '6355'
> and call_date between '08/20/07' and '09/20/07'
>
> the data I listed above will be call_duration. I'm not the best sql
> developer so how do I sum all of the call_duration results? Common sense
> says I can't use var/char as a datatype, can I sum a DateTime type? Can I
> use a varchar and use a Sum and Convert together?
>
> Is there a way to return the results in a Dataset/Datatable and do all
> this
> stuff in the C# code although resources will be killed? Any advice on this
> one would be greatly appreciated.
>
> Thank you for any advice.
>
> Kevin
>
> --
> Kevin C. Brown
> Developer

There can be up to 80,000+ rows in the table, I'm not sure this approach is
very practicle in this case.

Thank you though.

--
Kevin C. Brown
Developer


"sloan" wrote:

> First, create a small sample that demo's what you want.
>
> Here is a sample to get you started.
>
> set nocount on
>
> declare @holder table ( Column1 datetime )
>
> insert into @holder (Column1) values ('00:00:06')
>
> insert into @holder (Column1) values ('00:00:36')
>
> insert into @holder (Column1) values ('00:00:42')
>
> insert into @holder (Column1) values ('00:01:48')
>
> select sum(Column1) as MySum from @holder
>
> select avg(Column1) as MySum from @holder
>
>
> then post you're expecting results.
>
> DDL (data defintion language) is usually needed in these cases.
>
>
>
>
> "KevinB" <(E-Mail Removed)> wrote in message
> news:52A0BF07-96DE-468C-BCEC-(E-Mail Removed)...
> > This can either be a C# or Sql question. I have a SQL table full of data
> > and
> > I need to do a calculation on data that looks like this
> >
> > 00:00:06
> > 00:00:36
> > 00:00:42
> > 00:01:48
> >
> > my select statement will look something like this
> >
> > SELECT *
> > FROM [Microcall_Analysis].[dbo].[tbl_CallData]
> > where extension = '6355'
> > and call_date between '08/20/07' and '09/20/07'
> >
> > the data I listed above will be call_duration. I'm not the best sql
> > developer so how do I sum all of the call_duration results? Common sense
> > says I can't use var/char as a datatype, can I sum a DateTime type? Can I
> > use a varchar and use a Sum and Convert together?
> >
> > Is there a way to return the results in a Dataset/Datatable and do all
> > this
> > stuff in the C# code although resources will be killed? Any advice on this
> > one would be greatly appreciated.
> >
> > Thank you for any advice.
> >
> > Kevin
> >
> > --
> > Kevin C. Brown
> > Developer
>
>
>

summing duration is simple, not sure what a sum of call_time would be,
did you want the count?

select sum(datediff(ss,'00:00:00',call_duration) as totduration_secs
FROM [Microcall_Analysis].[dbo].[tbl_CallData]
where extension = '6355'
and call_date between '08/20/07' and '09/20/07'

-- bruce (sqlwork.com)

select
KevinB wrote:
> That query I included returns 1327 results, the call_duration is just one
> column. The actual query is in a stored procedure and would look like this.
> I need to sum call_duration and call_time but I'm not sure how to get those
> totals. Thanks again.
>
> SELECT [id]
> ,[pattern]
> ,[call_date]
> ,[call_time]
> ,[call_duration]
> ,[extension]
> ,[trunk]
> ,[dialed_number]
> ,[place_called]
> ,[cost]
> ,[caller_name]
> FROM [Microcall_Analysis].[dbo].[tbl_CallData]
> where extension = '6355'
> and call_date between '08/20/07' and '09/20/07'
>
>
>

Call duration can be added to a DateTime and add the values together. If you
are workign with SQL 2005, you can use C# to create an assembly in SQL
Server.

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

*************************************************
| Think outside the box!
|
*************************************************
"KevinB" <(E-Mail Removed)> wrote in message
news:52A0BF07-96DE-468C-BCEC-(E-Mail Removed)...
> This can either be a C# or Sql question. I have a SQL table full of data
> and
> I need to do a calculation on data that looks like this
>
> 00:00:06
> 00:00:36
> 00:00:42
> 00:01:48
>
> my select statement will look something like this
>
> SELECT *
> FROM [Microcall_Analysis].[dbo].[tbl_CallData]
> where extension = '6355'
> and call_date between '08/20/07' and '09/20/07'
>
> the data I listed above will be call_duration. I'm not the best sql
> developer so how do I sum all of the call_duration results? Common sense
> says I can't use var/char as a datatype, can I sum a DateTime type? Can I
> use a varchar and use a Sum and Convert together?
>
> Is there a way to return the results in a Dataset/Datatable and do all
> this
> stuff in the C# code although resources will be killed? Any advice on this
> one would be greatly appreciated.
>
> Thank you for any advice.
>
> Kevin
>
> --
> Kevin C. Brown
> Developer