C# or Sql Server

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
KevinB said:
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
Click to expand...

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 said:
KevinB said:
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
Click to expand...

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

LS
Click to expand...
 
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.
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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)
 
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!
|
*************************************************
 
Back
Top