Stored Procedure Giving null values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Stored Procedure Giving null values

Author

Topic

reflex2dotnet
Yak Posting Veteran

99 Posts

Posted - 2007-02-20 : 09:08:27

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.

I gave like this,
CREATE PROCEDURE getavgduration(@STARTDT DATETIME,@ENDDT DATETIME,@ID INT) AS

BEGIN

SELECT AVG(CAST(CAST(CONVERT(char(8), t1.duration, 108) AS datetime) AS numeric(18, 4)) * 24) AS AverageDuration
FROM table1 t1
INNER JOIN table2 t2 ON t1.caseid =t2.caseid
WHERE t2.ID=@ID
AND (t1.calldate between @STARTDT and @ENDDT)

END
GO
When i supplied dates and Id, which i have checked manually, and found to have data, result is Null

Can anyone please help to figure out this?

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-20 : 09:17:51

is your calldate with or without time ?

Post some sample data

reflex2dotnet
Yak Posting Veteran

99 Posts

Posted - 2007-02-20 : 09:28:13

IT IS with time
sample 2001-08-08 00:00:00
2006-05-08 14:50:00

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-20 : 09:33:42

[code]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)[/code]
Peter Larsson
Helsingborg, Sweden

reflex2dotnet
Yak Posting Veteran

99 Posts

Posted - 2007-02-20 : 10:23:31

Thanks

But I am getting the result as eg:5280, 6340
I would like to get as like 05:10:45

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!!!!

Or is that average returns like the above?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-20 : 10:32:45

[code]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)[/code]

Peter Larsson
Helsingborg, Sweden

reflex2dotnet
Yak Posting Veteran

99 Posts

Posted - 2007-02-20 : 10:41:50

Thanks

I tried this, but gives
The AVG function requires 1 arguments.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-20 : 10:50:26

quote:

Originally posted by Peso

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)

Peter Larsson
Helsingborg, Sweden

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

reflex2dotnet
Yak Posting Veteran

99 Posts

Posted - 2007-02-20 : 10:56:16

Thanks All
I often miss small small things(which is not good)

Trying to improve!!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources