Stored Procedure to get average

[dotnetstarter]

Hi All,

I am trying to write a stored procedure, where I need your help.

2 tables:
dbo.table1
- duration(time) eg: 00:01:34
-calldate
-caseid

dbo.table2
-ID
-caseid

I want to get the average of duration, between given dates supplied(eg @startDate, @endDate -- calldate) , and ID
One more issue is, I have the ID in table2 as the parameter.
In table2, there is no ID specified, only caseid is there.

How can I get this done.

Hope someone can help me

Thanks

[Kishore]

You can use AVG function to get the average of duration but you need to convert the duration into seconds so that it will give you average. You need to play around with following example.

[dotnetstarter]

Thanks for the reply

I tried with your peice of code.
But I am getting a null value for the inputs
I checked the data manually, and there is call duration between the dates which i gave

Can you please suggest some other modification??

[Kishore]

please dump few duration records here.
_________________
=================
Kish

[Kishore]

what is the duration field ? is it a datetime field?
_________________
=================
Kish

[dotnetstarter]

00:04:53
00:06:23
00:20:02

These are some sample data for duration

My calldate data also contain time part
as
2005-12-16 14:10:23

Duration field is nvarchar
CallDate field is smalldatetime

[Kishore]

Can you try calldate instead of duration in the query for a test?
_________________
=================
Kish

[dotnetstarter]

Gives
.000000

[Kishore]

what data type is duration field?
_________________
=================
Kish

[dotnetstarter]

Duration field is nvarchar
CallDate field is smalldatetime

[Kishore]

ok let me try something else. i'll test and tell you
_________________
=================
Kish

[dotnetstarter]

I tried with another select stmt, like

SELECT 24 * AVG(DATEDIFF(second, 0, t1.duration)) AS AverageDuration
FROM Table1 AS t1
INNER JOIN Table2 AS t2 ON t2.CaseID = t1.CaseID
WHERE t2.ID = @ID
AND t1.CallDate >= @StartDT
AND t1.CallDate < DATEADD(day, 1, @EndDT)

but it gives the average as 6246, 5623 etc

i put exec getavgduration_test '5/13/2005', '5/16/2005', 55
the data for the id 55, during this time period is

00:01:48
00:12:50
00:04:03
00:01:32
00:01:44
00:04:49
00:02:46

the result i am getting is 6192!!!!

Can you please go trough this select statement too

Thanks

[dotnetstarter]

Thanks

I got it worked with a new select stamtement
Just wanted to share

SELECT CONVERT(varchar, DATEADD(second, AVG(DATEDIFF(second, 0, t1.duration)), 0), 108) AS AverageDuration
FROM Table1 AS t1
INNER JOIN Table2 AS t2 ON t2.CaseID = t1.CaseID
WHERE t2.ID = @ID
AND t1.CallDate >= @StartDT
AND t1.CallDate < DATEADD(day, 1, @EndDT)

[Kishore]

Ok great! I was something similar but didnt work on my SQL server. I use SQL Server 2000.

Any way nice to see that you have sorted the isssue.

Goodluck!
_________________
=================
Kish