C# or Sql Server

[=?Utf-8?B?S2V2aW5C?=]

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

[Lloyd Sheen]

"KevinB" <Ke*********@DarbyDentalSupply.comwrote in message
news:52**********************************@microsof t.com...

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

[=?Utf-8?B?S2V2aW5C?=]

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" <Ke*********@DarbyDentalSupply.comwrote in message
news:52**********************************@microsof t.com...

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

[sloan]

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" <Ke*********@DarbyDentalSupply.comwrote in message
news:52**********************************@microsof t.com...

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

[bruce barker]

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'

[Cowboy \(Gregory A. Beamer\)]

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" <Ke*********@DarbyDentalSupply.comwrote in message
news:52**********************************@microsof t.com...

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