August 6, 2009 at 7:30 am
I have a table with DATE as one of Columns which is in days. My problems is to calculate the MTD moving average for a column called MEASUREVALUE on daily basis as well as YTD. Please assist
August 6, 2009 at 1:12 pm
Read the article at the first link in my signature below. Provide a CREATE TABLE statement and the code to populate it with 10 rows of data using the methods in the article.
This is actually a simple problem but I need you to provide that very simple test harness... I've got too many people to help to build one for each person. Thanks.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 7, 2009 at 1:08 am
Please assist me calculate MTD and YTD daily moving average from the example below, Please
Drop table #Calendar
-- simple calendar table
CREATE TABLE #Calendar
(
dt SMALLDATETIME NOT NULL PRIMARY KEY,
IsWeekday AS CONVERT(BIT, CASE WHEN DATEPART(DW, dt) IN (1,7) THEN 0 ELSE 1
END),
IsHoliday BIT NOT NULL DEFAULT 0
);
GO
Select * from #Calendar
-- insert two months of data
INSERT #Calendar(dt)
SELECT DISTINCT DATEADD(DAY, number, '20071101')
FROM master..spt_values
WHERE number BETWEEN 0 AND 60
ORDER BY 1;
-- make at least one holiday
UPDATE #Calendar
SET IsHoliday = 1
WHERE DATEPART(MONTH, dt) = 12
AND DAY(dt) = 25;
-- simple stock table
CREATE TABLE #StocksWithMissingData
(
Stock CHAR(4),
dt SMALLDATETIME,
ClosePrice DECIMAL(6,2)
);
-- two months of data, leaving out a couple days intentionally
INSERT #StocksWithMissingData
SELECT 'abc', '20071101', 36.75
UNION SELECT 'abc', '20071102', 35.65
UNION SELECT 'abc', '20071105', 36.25
UNION SELECT 'abc', '20071106', 35.50
UNION SELECT 'abc', '20071107', 35.40
UNION SELECT 'abc', '20071108', 34.50
UNION SELECT 'abc', '20071109', 37.50
UNION SELECT 'abc', '20071112', 36.50
UNION SELECT 'abc', '20071113', 35.90
UNION SELECT 'abc', '20071114', 32.50
UNION SELECT 'abc', '20071115', 35.70
UNION SELECT 'abc', '20071116', 35.80
UNION SELECT 'abc', '20071119', 31.50
UNION SELECT 'abc', '20071120', 39.50
UNION SELECT 'abc', '20071121', 41.70
UNION SELECT 'abc', '20071122', 45.70
UNION SELECT 'abc', '20071123', 42.90
--UNION SELECT 'abc', '20071126', 41.80
UNION SELECT 'abc', '20071127', 44.70
UNION SELECT 'abc', '20071128', 43.20
UNION SELECT 'abc', '20071129', 41.35
UNION SELECT 'abc', '20071130', 42.70
UNION SELECT 'abc', '20071203', 42.50
UNION SELECT 'abc', '20071204', 43.25
UNION SELECT 'abc', '20071205', 42.97
UNION SELECT 'abc', '20071206', 43.78
UNION SELECT 'abc', '20071207', 45.80
UNION SELECT 'abc', '20071210', 43.65
UNION SELECT 'abc', '20071211', 48.25
--UNION SELECT 'abc', '20071212', 40.70
UNION SELECT 'abc', '20071213', 41.25
UNION SELECT 'abc', '20071214', 44.76
UNION SELECT 'abc', '20071217', 44.14
UNION SELECT 'abc', '20071218', 42.90
UNION SELECT 'abc', '20071219', 43.45
UNION SELECT 'abc', '20071220', 45.60
UNION SELECT 'abc', '20071221', 41.25
UNION SELECT 'abc', '20071224', 42.15
UNION SELECT 'abc', '20071226', 44.00
UNION SELECT 'abc', '20071227', 39.55
UNION SELECT 'abc', '20071228', 41.20
UNION SELECT 'abc', '20071231', 46.15;
-- find the gaps
SELECT c.dt
FROM #Calendar c
LEFT OUTER JOIN #StocksWithMissingData s
ON c.dt = s.dt
AND s.Stock = 'abc'
WHERE c.IsHoliday = 0
AND c.IsWeekday = 1
AND s.dt IS NULL;
-- find moving average
SELECT s.Stock, s.dt as Date,s.ClosePrice, MovingAVG =
(
SELECT AVG(ClosePrice)
FROM
(
SELECT TOP 21 s2.dt, cdt = c.dt, ClosePrice
FROM #StocksWithMissingData s2
INNER JOIN #Calendar c
ON s2.dt = c.dt
WHERE c.IsHoliday = 0
AND c.IsWeekday = 1
AND s2.Stock = s.Stock
AND s2.dt <= s.dt
ORDER BY s2.dt DESC
) x
)
FROM #StocksWithMissingData s
ORDER BY s.dt;
---DROP TABLE #Calendar, #StocksWithMissingData;
GO
August 7, 2009 at 9:05 am
VERY well done on the setup but that brings up another question... why do you need help? I've not run the code but it appears that you've already solved the problem (albeit, with a rather slow triangular join).
Also, do you want to include missing days in the running average or not?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply