- Home /

Hi, I am trying to calculate an exponential moving average. The components required are all available but the calculation is returning 'Nulls' in the DMA1 column. The RawLastPrice is inserted into a new row. DMA1 is a column which needs to be calculated. In order to calculate @DMA1, I need to reference the previous DMA1 number in the calc. The calc itself is sound but I'm missing something. Can you please help?

declare @DMA1Var int declare @PrevDMA1 decimal(29,12) declare @DMA1 decimal(29,12) select @sql = N'select @PrevDMA1 = @DMA1 from ' + quotename(@TableName) + N' where id = @PrevID ' select @sql = N'select @DMA1 = cast(((RawLastPrice - @PrevDMA1) * (2 / (@DMA1Var + 1)) + @PrevDMA1) as decimal(29,12)) from ' + quotename(@TableName) select @params = N'@DMA1 decimal(29,12) OUTPUT, @NewID int, @DMA1Var int, @PrevDMA1 decimal(29,12) OUTPUT, @PrevID int' EXEC sp_executesql @sql, @params, @DMA1 = @DMA1 OUTPUT, @NewID = @NewID, @DMA1Var = @DMA1Var, @PrevDMA1 = @PrevDMA1 OUTPUT,@PrevID = @PreviousID

Thx

Comment

**Answer** by Squirrel 1
·
Jan 11, 2010 at 01:16 AM

```
declare @DMA1Var int
declare @PrevDMA1 decimal(29,12)
declare @DMA1 decimal(29,12)
-- changed the select statement
select @sql = N'select @PrevDMA1 = DMA1 from ' + quotename(@TableName) + N' where id = @PrevID '
-- added sp_executesql here
EXEC sp_executesql @sql, N'@PrevID int, @PrevDMA1 decimal(29,12) OUTPUT', @PrevID, @PrevDMA1 OUTPUT
select @sql = N'select @DMA1 = cast(((RawLastPrice - @PrevDMA1) * (2 / (@DMA1Var + 1)) + @PrevDMA1) as decimal(29,12)) from ' + quotename(@TableName)
select @params = N'@DMA1 decimal(29,12) OUTPUT, @NewID int, @DMA1Var int, @PrevDMA1 decimal(29,12) OUTPUT, @PrevID int'
EXEC sp_executesql @sql, @params, @DMA1 = @DMA1 OUTPUT, @NewID = @NewID, @DMA1Var = @DMA1Var, @PrevDMA1 = @PrevDMA1 OUTPUT,@PrevID = @PreviousID
```

Copyright 2019 Redgate Software. Privacy Policy

- Anonymous
- Sign in
- Create
- Ask a question
- Spaces
- Ask SSC Site Issues (meta-askssc)
- Explore
- Topics
- Questions
- Users
- Badges