moving average past three 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)

moving average past three month

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-01-23 : 16:02:12

for moving average for sample data of past three month from today meaning using GETDATE() how do I go about doing this following awesome SELECT

SELECT DATEADD(DAY, p.ActualDate, '19000101') AS dt,
AVG(p.Rate) AS SimpleMovingAvg,
SUM(
CASE p.dt
WHEN 'dt0' THEN p.r0
WHEN 'dt1' THEN p.r1
WHEN 'dt2' THEN p.r2
ELSE 0
END
) AS WeightedMovingAvg
FROM (
SELECT Rate,
DATEDIFF(DAY, '19000101', dt) AS dt0,
0.7 * Rate AS r0,
DATEDIFF(DAY, '18991231', dt) AS dt1,
0.2 * Rate AS r1,
DATEDIFF(DAY, '18991230', dt) AS dt2,
0.1 * Rate AS r2
FROM @Sample
) AS y
UNPIVOT (
ActualDate
FOR dt IN (y.dt0, y.dt1, y.dt2)
) AS p
GROUP BY p.ActualDate
HAVING MIN(p.dt) = 'dt0'
ORDER BY p.ActualDate

obrigadu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-24 : 12:04:35

can you illustrate with some sample data?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-25 : 03:19:40

Why start a new topic with same question?
See http://weblogs.sqlteam.com/peterl/archive/2007/12/10/Cursor-is-really-faster-than-set-based-solution.aspx

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-25 : 03:20:44

In your other question, I posted

FROM @Sample
WHERE dt >= DATEADD(MONTH, -3, GETDATE())

E 12°55'05.63"
N 56°04'39.26"

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-01-26 : 10:17:37

Peso

sorry to have posted new question same topic.

here is the sample data

DownTimeID FromDownTimeDate ToDownTimeDate
154 2008-02-18 18:10:00.000 2008-02-18 18:45:00.000
446 2008-04-04 08:00:00.000 2008-04-04 08:30:00.000
5 2008-01-10 20:20:00.000 2008-01-10 22:50:00.000
6 2008-01-12 10:30:00.000 2008-01-12 11:00:00.000

It is a DownTimeLog table with following structure..we keep logs in it for metal saws.

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-26 : 10:21:41

and what should be o/p out of this?

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-01-26 : 10:28:02

moving average for past three months..

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-01-26 : 10:54:24

here it is in another formar

Month Year SumMinutes
10 2008 45540
12 2008 7645
1 2009 23000