need help on a Function - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

need help on a Function

Author

Topic

P@go
Starting Member

3 Posts

Posted - 2008-07-27 : 20:58:58

Hi all,
I'm new here and this is my first post on this forum. (i'm not a sql Developer...yet)
I need to calculate the diference between values of the actual and the previuos record on a table. For this i've developed the function below:
CREATE function [dbo].[EnergyTotalizer]
(@TagN as varchar(40), @DataHora as datetime, @Valor real)
Returns real
as begin
declare @lastValor real
select top 1 @lastValor = [Value]
from dbo.tblReport
where tagname = @tagN and Datahora < @DataHora
order by Datahora desc
select @lastValor = isnull(@lastvalor, 0)
if @Valor >= @lastValor
set @Valor = @Valor - @lastValor
return @Valor
end

The problem is that some of this values appear Null (they come from a third party application that inserts on the table). On my calculation i can replace a null value for zero but the next value is would appear the raw value. What i need is to keep doing the calcs with the last good value known if the actual is null.

Can someone help me on this?
(hope not to i've been confuse)

Regards,

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-27 : 21:56:42

[code]WHERE [Value] is not null
and tagname = @tagN
and Datahora < @DataHora[/code]

KH
[spoiler]Time is always against us[/spoiler]

P@go
Starting Member

3 Posts

Posted - 2008-07-29 : 12:33:29

Thank you khtan,

It works.
I was trying it but before and it didn't work. Probably i was with problems with my machine.

But now i'm with another question on the same code.
if the value is null i want to do this calc but the result must be divided by two (like an average).

I can't get any working idea with this function!
Can give a tip.

Regards,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-29 : 12:48:44

quote:
Originally posted by P@go

Thank you khtan,

It works.
I was trying it but before and it didn't work. Probably i was with problems with my machine.

But now i'm with another question on the same code.
if the value is null i want to do this calc but the result must be divided by two (like an average).

I can't get any working idea with this function!
Can give a tip.

Regards,

if @Valor >= @lastValor
set @Valor = case when @lastValor >0 then @Valor - @lastValor else @Valor/2 end

P@go
Starting Member

3 Posts

Posted - 2008-07-30 : 12:36:53

oh!
I was trying to use the CASE stmnt on the Select.

I've tested your solution and it doesn't work.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-30 : 12:56:45

quote:
Originally posted by P@go

oh!
I was trying to use the CASE stmnt on the Select.

I've tested your solution and it doesn't work.

why what was the error?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources