Nice, Thanks!:-)

The real code is more complex, thanks Lutz!:-)

Thanks all for a response...:-)

I will try to be clear on my intended purpose...

I am pulling data up from a VARCHAR(MAX) field that is stored like this

data|data|data|etc

I turn this string...

Ha ha...Done !

Thanks...for the help !:-):-D:-)

Thanks, I added one more complication, another grouping field called 'Type', I get code errors..

Any ideas..

IF OBJECT_ID('tempdb..#test') IS NOT NULL DROP TABLE #test;

CREATE TABLE #test

(

[Date] DATETIME,

[Type] VARCHAR(10),

Symbol VARCHAR(10),

UpDown INT,

)

INSERT INTO...

nice...thanks

here is my final code for others

DECLARE @Symbol VARCHAR(10)

DECLARE @Period INT

DECLARE @Period_sum DECIMAL(12,6)

DECLARE @anchor INT

DECLARE @moving_sum DECIMAL(12,6)

DECLARE @initial_sum DECIMAL(12,6)

SET @Symbol ='AAPL'

SET @Period = 20

IF OBJECT_ID('tempdb..#t1 ') IS NOT NULL...

Here is the data a full code

GO

CREATE TABLE [DIM_Data_OHLC](

[RecID] [int] IDENTITY(1,1) NOT NULL,

[Symbol] [varchar](10) NOT NULL,

[Date] [datetime] NOT NULL,

[Close] [float] NULL

CONSTRAINT [PK_DIM_Data_OHLC] PRIMARY KEY NONCLUSTERED

(

[Symbol] ASC,

[Date] ASC

)WITH (PAD_INDEX...

Fair enough how do I post 6000 records on this forum for testing

UPDATE: Do go away I will post the data...I am working it out !

On a large dataset..

if you change

ON a.n - 20 = b.n

to this

ON a.n - @Intervals =b.n

The calc is a lot slower, from 1 sec to 12 sec...

I am try to allow the SMA variable to be set by the variable @Intervals

This code works for a small data set, but one with 10,000 records, its slow

--Create our...

This is the code posted by 'Gabriel P' on Posted 3/8/2010 5:23 PM

declare @intervals int, @initial_sum decimal(8,2)

declare @anchor int, @moving_sum decimal(8,2)

SET @INTERVALS = 20

with t1 as (select...

MSSQL 2005...

Well, I tried with the SMA code from article and all I get is ERRORS...

Does any one have the final script that works ??

Here is code I copied..

--Create our...

I have under a 1000 blos posts, so there is not a lot of data to worry about...

but here you go...

USE [DB785342]

GO

/****** Object: UserDefinedFunction [dbo].[fnBlogPosts] Script...

Thanks, I ended up creating a function that return a table...all seams good !:-)