Rolling Average of the next 3 month

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)

Rolling Average of the next 3 month

Author

Topic

ravishwor
Starting Member

20 Posts

Posted - 2009-02-22 : 22:36:29

Hi Folks,

I need to calculate the Rolling Average in a Column looking ahead for the next 3 months.

Here is the sample data;

YYYYMM Mth# SKU Whse Qty
=======================================================
200901 1 WHA0100 B 2012.06
200902 2 WHA0100 B 2012.06
200903 3 WHA0100 B 2012.06
200904 4 WHA0100 B 2012.06
200905 5 WHA0100 B 2012.06
200906 6 WHA0100 B 2012.06
200907 7 WHA0100 B 2194.06
200908 8 WHA0100 B 2194.06
200909 9 WHA0100 B 2194.06
200910 10 WHA0100 B 2194.06
200911 11 WHA0100 B 2194.06
200912 12 WHA0100 B 2194.06
201001 1 WHA0100 B 2194.06
201002 2 WHA0100 B 2194.06
201003 3 WHA0100 B 2194.06
201004 4 WHA0100 B 2194.06
200901 1 WHA0100 S 4107.83
200902 2 WHA0100 S 4107.83
200903 3 WHA0100 S 4107.83
200904 4 WHA0100 S 4107.83
200905 5 WHA0100 S 4107.83
200906 6 WHA0100 S 4107.83
200907 7 WHA0100 S 4107.83
200908 8 WHA0100 S 4107.83
200909 9 WHA0100 S 4107.83
200910 10 WHA0100 S 4107.83
200911 11 WHA0100 S 4107.83
200912 12 WHA0100 S 4107.83
201001 1 WHA0100 S 4107.83
201002 2 WHA0100 S 4107.83
201003 3 WHA0100 S 4107.83
201004 4 WHA0100 S 4107.83

Thx. in advance.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-22 : 23:07:00

And your Expected output?

ravishwor
Starting Member

20 Posts

Posted - 2009-02-22 : 23:15:28

My Expected output would look like this-

YYYYMM Mth# SKU Whse Qty Avg3MthQty
=======================================================
200901 1 WHA0100 B 2012.06 2012.06
200902 2 WHA0100 B 2012.06 2012.06
200903 3 WHA0100 B 2012.06 2012.06
200904 4 WHA0100 B 2012.06
200905 5 WHA0100 B 2012.06

ravishwor
Starting Member

20 Posts

Posted - 2009-02-23 : 16:28:48

is this possible in sql? All i need is an new column "Avg3MthQty" that is calculated and is the average of the next 3 months of column "Qty".

Thx all.

ravishwor
Starting Member

20 Posts

Posted - 2009-02-24 : 00:33:12

This may be a better example to visualise my expected output; any help from sql gurus out there is much appreciated. Thx.

YYYYMM Mth# SKU Whse Qty Avg3MthQty
=======================================================
200901 1 WHA0100 B 2 3 ie, Avg(3+4+2)
200902 2 WHA0100 B 3 2.3 ie, Avg(4+2+1)
200903 3 WHA0100 B 4
200904 4 WHA0100 B 2
200905 5 WHA0100 B 1

xpandre
Posting Yak Master

212 Posts

Posted - 2009-02-24 : 02:57:40

Not very sure, but you try this out. Hope this helps.

drop table a 

create table a(
yyyy	int,
mm	int,
sku	varchar(max),
qty	decimal(10,2)
)

insert into a values (2009,1,'WHA0100B',2012.06)
insert into a values (2009,2,'WHA0100B',2012.06)
insert into a values (2009,3,'WHA0100B',2012.06)
insert into a values (2009,4,'WHA0100B',2012.06)
insert into a values (2009,5,'WHA0100B',2012.06)
insert into a values (2009,6,'WHA0100B',2012.06)
insert into a values (2009,7,'WHA0100B',2194.06)
insert into a values (2009,8,'WHA0100B',2194.06)
insert into a values (2009,9,'WHA0100B',2194.06)
insert into a values (2009,10,'WHA0100B',2194.06)
insert into a values (2009,11,'WHA0100B',2194.06)
insert into a values (2009,12,'WHA0100B',2194.06)
insert into a values (2010,1,'WHA0100B',2194.06)
insert into a values (2010,2,'WHA0100B',2194.06)
insert into a values (2010,3,'WHA0100B',2194.06)
insert into a values (2010,4,'WHA0100B',2194.06)
insert into a values (2009,1,'WHA0100S',4107.83)
insert into a values (2009,2,'WHA0100S',4107.83)
insert into a values (2009,3,'WHA0100S',4107.83)
insert into a values (2009,4,'WHA0100S',4107.83)
insert into a values (2009,5,'WHA0100S',4107.83)
insert into a values (2009,6,'WHA0100S',4107.83)
insert into a values (2009,7,'WHA0100S',4107.83)
insert into a values (2009,8,'WHA0100S',4107.83)
insert into a values (2009,9,'WHA0100S',4107.83)
insert into a values (2009,10,'WHA0100S',4107.83)
insert into a values (2009,11,'WHA0100S',4107.83)
insert into a values (2009,12,'WHA0100S',4107.83)
insert into a values (2010,1,'WHA0100S',4107.83)
insert into a values (2010,2,'WHA0100S',4107.83)
insert into a values (2010,3,'WHA0100S',4107.83)
insert into a values (2010,4,'WHA0100S',4107.83)


select yyyy,mm,qty,
(
select cast(avg(qty) as decimal(10,2)) from a g 
where cast(cast(g.mm as varchar)+'/01/'+cast(g.yyyy as varchar) as datetime) between

dateadd(mm,1,cast(cast(h.mm as varchar)+'/01/'+cast(h.yyyy as varchar) as datetime)) and 
dateadd(mm,3,cast(cast(h.mm as varchar)+'/01/'+cast(h.yyyy as varchar) as datetime))
 and h.sku = g.sku
) from a h

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-24 : 10:24:23

its possible,


SELECT *,COALESCE(t1.RollAvg,0)
FROM Yourtable t
OUTER APPLY (SELECT AVG(Qty*1.0) AS RollAvg
             FROM yourtable
             WHERE SKU =t.SKU 
             AND Whse = t.Whse
             AND [Mth#]<= t.[Mth#]+3
             )t1

i assume your [Mth#] will be always continuos

ravishwor
Starting Member

20 Posts

Posted - 2009-02-24 : 17:25:10

Hi visakh16,

thx for you help on this one. Your method does not calc the avg accurately. Not sure what is mising or wrong. Yes, the month_no is continous. To better explain, here are more details:

DROP TABLE fc

CREATE TABLE [dbo].[fc](
[yyyymm] [int] NULL,
[month_no] [int] NOT NULL,
[stock_code] [char](16) NOT NULL,
[whse_code] [char](1) NOT NULL,
[fcqty] [float] NULL
) ON [PRIMARY]

INSERT INTO FC VALUES (200901, 1, 'CDK9645', 'B', 23)
INSERT INTO FC VALUES (200901, 1, 'CDK9645', 'S', 25)
INSERT INTO FC VALUES (200902, 2, 'CDK9645', 'B', 23)
INSERT INTO FC VALUES (200902, 2, 'CDK9645', 'S', 25)
INSERT INTO FC VALUES (200903, 3, 'CDK9645', 'B', 6)
INSERT INTO FC VALUES (200903, 3, 'CDK9645', 'S', 154)
INSERT INTO FC VALUES (200904, 4, 'CDK9645', 'B', 4)
INSERT INTO FC VALUES (200904, 4, 'CDK9645', 'S', 45)
INSERT INTO FC VALUES (200905, 5, 'CDK9645', 'B', 4)
INSERT INTO FC VALUES (200905, 5, 'CDK9645', 'S', 45)
INSERT INTO FC VALUES (200906, 6, 'CDK9645', 'B', 4)
INSERT INTO FC VALUES (200906, 6, 'CDK9645', 'S', 45)
INSERT INTO FC VALUES (200907, 7, 'CDK9645', 'B', 4)
INSERT INTO FC VALUES (200907, 7, 'CDK9645', 'S', 45)
INSERT INTO FC VALUES (200908, 8, 'CDK9645', 'B', 4)
INSERT INTO FC VALUES (200908, 8, 'CDK9645', 'S', 45)
INSERT INTO FC VALUES (200909, 9, 'CDK9645', 'B', 4)
INSERT INTO FC VALUES (200910, 10, 'CDK9645', 'B', 4)
INSERT INTO FC VALUES (200911, 11, 'CDK9645', 'B', 4)
INSERT INTO FC VALUES (200912, 12, 'CDK9645', 'B', 4)
INSERT INTO FC VALUES (201001, 1, 'CDK9645', 'B', 4)
INSERT INTO FC VALUES (201002, 2, 'CDK9645', 'B', 4)
INSERT INTO FC VALUES (201003, 3, 'CDK9645', 'B', 4)
INSERT INTO FC VALUES (201004, 4, 'CDK9645', 'B', 4)

Thx for you help.

ravishwor
Starting Member

20 Posts

Posted - 2009-02-24 : 18:24:36

Hi xpandre,

I modified your solution to suit my needs. But i get NULL for the avg_3mth calculation on all columns. I think we are close to the solution. Thx.

DROP TABLE fc

CREATE TABLE [dbo].[fc](
[yyyymm] [int] NULL,
[month_no] [int] NOT NULL,
[stock_code] [char](16) NOT NULL,
[whse_code] [char](1) NOT NULL,
[fcqty] [float] NULL
) ON [PRIMARY]

INSERT INTO FC VALUES (200901, 1, 'CDK9645', 'B', 23)
INSERT INTO FC VALUES (200901, 1, 'CDK9645', 'S', 25)
INSERT INTO FC VALUES (200902, 2, 'CDK9645', 'B', 23)
INSERT INTO FC VALUES (200902, 2, 'CDK9645', 'S', 25)
INSERT INTO FC VALUES (200903, 3, 'CDK9645', 'B', 6)
INSERT INTO FC VALUES (200903, 3, 'CDK9645', 'S', 154)
INSERT INTO FC VALUES (200904, 4, 'CDK9645', 'B', 4)
INSERT INTO FC VALUES (200904, 4, 'CDK9645', 'S', 45)
INSERT INTO FC VALUES (200905, 5, 'CDK9645', 'B', 4)
INSERT INTO FC VALUES (200905, 5, 'CDK9645', 'S', 45)
INSERT INTO FC VALUES (200906, 6, 'CDK9645', 'B', 4)
INSERT INTO FC VALUES (200906, 6, 'CDK9645', 'S', 45)
INSERT INTO FC VALUES (200907, 7, 'CDK9645', 'B', 4)
INSERT INTO FC VALUES (200907, 7, 'CDK9645', 'S', 45)
INSERT INTO FC VALUES (200908, 8, 'CDK9645', 'B', 4)
INSERT INTO FC VALUES (200908, 8, 'CDK9645', 'S', 45)
INSERT INTO FC VALUES (200909, 9, 'CDK9645', 'B', 4)
INSERT INTO FC VALUES (200910, 10, 'CDK9645', 'B', 4)
INSERT INTO FC VALUES (200911, 11, 'CDK9645', 'B', 4)
INSERT INTO FC VALUES (200912, 12, 'CDK9645', 'B', 4)
INSERT INTO FC VALUES (201001, 1, 'CDK9645', 'B', 4)
INSERT INTO FC VALUES (201002, 2, 'CDK9645', 'B', 4)
INSERT INTO FC VALUES (201003, 3, 'CDK9645', 'B', 4)
INSERT INTO FC VALUES (201004, 4, 'CDK9645', 'B', 4)

select stock_code, whse_code,yyyymm, month_no, fcqty,
(
select avg(fcqty) from FC g
where
cast( (convert(varchar, yyyymm) + '01') as datetime) between
dateadd(mm, 1, cast( (convert(varchar, yyyymm) + '01') as datetime)) and
dateadd(mm, 3, cast( (convert(varchar, yyyymm) + '01') as datetime))
and h.stock_code = g.stock_code
and h.whse_code = g.whse_code
) as avg_nxt3mth
from FC h

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-25 : 09:58:58

quote:
Originally posted by ravishwor

Hi visakh16,

thx for you help on this one. Your method does not calc the avg accurately. Not sure what is mising or wrong. Yes, the month_no is continous. To better explain, here are more details:

DROP TABLE fc

CREATE TABLE [dbo].[fc](
[yyyymm] [int] NULL,
[month_no] [int] NOT NULL,
[stock_code] [char](16) NOT NULL,
[whse_code] [char](1) NOT NULL,
[fcqty] [float] NULL
) ON [PRIMARY]

INSERT INTO FC VALUES (200901, 1, 'CDK9645', 'B', 23)
INSERT INTO FC VALUES (200901, 1, 'CDK9645', 'S', 25)
INSERT INTO FC VALUES (200902, 2, 'CDK9645', 'B', 23)
INSERT INTO FC VALUES (200902, 2, 'CDK9645', 'S', 25)
INSERT INTO FC VALUES (200903, 3, 'CDK9645', 'B', 6)
INSERT INTO FC VALUES (200903, 3, 'CDK9645', 'S', 154)
INSERT INTO FC VALUES (200904, 4, 'CDK9645', 'B', 4)
INSERT INTO FC VALUES (200904, 4, 'CDK9645', 'S', 45)
INSERT INTO FC VALUES (200905, 5, 'CDK9645', 'B', 4)
INSERT INTO FC VALUES (200905, 5, 'CDK9645', 'S', 45)
INSERT INTO FC VALUES (200906, 6, 'CDK9645', 'B', 4)
INSERT INTO FC VALUES (200906, 6, 'CDK9645', 'S', 45)
INSERT INTO FC VALUES (200907, 7, 'CDK9645', 'B', 4)
INSERT INTO FC VALUES (200907, 7, 'CDK9645', 'S', 45)
INSERT INTO FC VALUES (200908, 8, 'CDK9645', 'B', 4)
INSERT INTO FC VALUES (200908, 8, 'CDK9645', 'S', 45)
INSERT INTO FC VALUES (200909, 9, 'CDK9645', 'B', 4)
INSERT INTO FC VALUES (200910, 10, 'CDK9645', 'B', 4)
INSERT INTO FC VALUES (200911, 11, 'CDK9645', 'B', 4)
INSERT INTO FC VALUES (200912, 12, 'CDK9645', 'B', 4)
INSERT INTO FC VALUES (201001, 1, 'CDK9645', 'B', 4)
INSERT INTO FC VALUES (201002, 2, 'CDK9645', 'B', 4)
INSERT INTO FC VALUES (201003, 3, 'CDK9645', 'B', 4)
INSERT INTO FC VALUES (201004, 4, 'CDK9645', 'B', 4)

Thx for you help.

Ok try this modified one


SELECT *,COALESCE(t1.RollAvg,0)
FROM Yourtable t
OUTER APPLY (SELECT AVG(Qty*1.0) AS RollAvg
             FROM yourtable
             WHERE SKU =t.SKU 
             AND Whse = t.Whse
             AND [Mth#]>t.[Mth#]
             AND [Mth#]<= t.[Mth#]+3
             )t1

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-02-25 : 10:03:38

thess might help?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=93911&whichpage=1

http://oreilly.com/catalog/transqlcook/chapter/ch08.html

ravishwor
Starting Member

20 Posts

Posted - 2009-02-25 : 20:19:37

This one worked well; i forgot to reference the table alias earlier.

select h.stock_code, h.whse_code, h.yyyymm, h.month_no, h.fcqty,
(
select avg(g.fcqty) from FC g
where
cast( (convert(varchar, g.yyyymm) + '01') as datetime) between
dateadd(mm, 1, cast( (convert(varchar, h.yyyymm) + '01') as datetime)) and
dateadd(mm, 3, cast( (convert(varchar, h.yyyymm) + '01') as datetime))
and h.stock_code = g.stock_code
and h.whse_code = g.whse_code
) as avg_nxt3mth
from FC h
order by stock_code, whse_code, yyyymm

Thx a ton to xpandre.

ravishwor
Starting Member

20 Posts

Posted - 2009-02-25 : 20:21:13

quote:
Originally posted by yosiasz

thess might help?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=93911&whichpage=1

http://oreilly.com/catalog/transqlcook/chapter/ch08.html

Ths for the links yosiasz. I ahd already looked at link#1 but it was not for me. #2 is very useful. Thx.

ravishwor
Starting Member

20 Posts

Posted - 2009-02-25 : 20:28:01

quote:
Originally posted by visakh16

quote:
Originally posted by ravishwor

Hi visakh16,

thx for you help on this one. Your method does not calc the avg accurately. Not sure what is mising or wrong. Yes, the month_no is continous. To better explain, here are more details:

DROP TABLE fc

CREATE TABLE [dbo].[fc](
[yyyymm] [int] NULL,
[month_no] [int] NOT NULL,
[stock_code] [char](16) NOT NULL,
[whse_code] [char](1) NOT NULL,
[fcqty] [float] NULL
) ON [PRIMARY]

INSERT INTO FC VALUES (200901, 1, 'CDK9645', 'B', 23)
INSERT INTO FC VALUES (200901, 1, 'CDK9645', 'S', 25)
INSERT INTO FC VALUES (200902, 2, 'CDK9645', 'B', 23)
INSERT INTO FC VALUES (200902, 2, 'CDK9645', 'S', 25)
INSERT INTO FC VALUES (200903, 3, 'CDK9645', 'B', 6)
INSERT INTO FC VALUES (200903, 3, 'CDK9645', 'S', 154)
INSERT INTO FC VALUES (200904, 4, 'CDK9645', 'B', 4)
INSERT INTO FC VALUES (200904, 4, 'CDK9645', 'S', 45)
INSERT INTO FC VALUES (200905, 5, 'CDK9645', 'B', 4)
INSERT INTO FC VALUES (200905, 5, 'CDK9645', 'S', 45)
INSERT INTO FC VALUES (200906, 6, 'CDK9645', 'B', 4)
INSERT INTO FC VALUES (200906, 6, 'CDK9645', 'S', 45)
INSERT INTO FC VALUES (200907, 7, 'CDK9645', 'B', 4)
INSERT INTO FC VALUES (200907, 7, 'CDK9645', 'S', 45)
INSERT INTO FC VALUES (200908, 8, 'CDK9645', 'B', 4)
INSERT INTO FC VALUES (200908, 8, 'CDK9645', 'S', 45)
INSERT INTO FC VALUES (200909, 9, 'CDK9645', 'B', 4)
INSERT INTO FC VALUES (200910, 10, 'CDK9645', 'B', 4)
INSERT INTO FC VALUES (200911, 11, 'CDK9645', 'B', 4)
INSERT INTO FC VALUES (200912, 12, 'CDK9645', 'B', 4)
INSERT INTO FC VALUES (201001, 1, 'CDK9645', 'B', 4)
INSERT INTO FC VALUES (201002, 2, 'CDK9645', 'B', 4)
INSERT INTO FC VALUES (201003, 3, 'CDK9645', 'B', 4)
INSERT INTO FC VALUES (201004, 4, 'CDK9645', 'B', 4)

Thx for you help.

Ok try this modified one

SELECT *,COALESCE(t1.RollAvg,0)
FROM Yourtable t
OUTER APPLY (SELECT AVG(Qty*1.0) AS RollAvg
FROM yourtable
WHERE SKU =t.SKU
AND Whse = t.Whse
AND [Mth#]>t.[Mth#]
AND [Mth#]<= t.[Mth#]+3
)t1

Hi visakh16,

It still calculates incorrectly for the first2-3 rows for some reason. Here is the result;
Notice the COlumn 'XL-avg' is the correct value that i calculeted in excel. See the difference between '3mthavg' and 'XL-avg'. But only in rows 1-2 and rows 12-14 and row 16.

yyyymm mth stk whse fcqty 3mthavg XL-avg
200901 1 CDK9645 B 23 7.5 11
200902 2 CDK9645 B 23 4.4 4.66
200903 3 CDK9645 B 6 4 4
200904 4 CDK9645 B 4 4 4
200905 5 CDK9645 B 4 4 4
200906 6 CDK9645 B 4 4 4
200907 7 CDK9645 B 4 4 4
200908 8 CDK9645 B 4 4 4
200909 9 CDK9645 B 4 4 4
200910 10 CDK9645 B 4 4 4
200911 11 CDK9645 B 4 4 4
200912 12 CDK9645 B 4 0 4
201001 1 CDK9645 B 4 7.5 4
201002 2 CDK9645 B 4 4.4 4
201003 3 CDK9645 B 4 4 4
201004 4 CDK9645 B 4 4 null
200901 1 CDK9645 S 25 74.6 74.6
200902 2 CDK9645 S 25 81.3 81.3
200903 3 CDK9645 S 154 45 45
200904 4 CDK9645 S 45 45 45
200905 5 CDK9645 S 45 45 45
200906 6 CDK9645 S 45 45 45
200907 7 CDK9645 S 45 45 45
200908 8 CDK9645 S 45 0 #DIV/0!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources