Calling stored procedure - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Calling stored procedure

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2009-05-15 : 13:39:29

I have a stored procedure that generates usage buckets by month. Data looks like the following:
StoredProcedure naem = UsagebyPeriod


YearVal  Jan  Feb  March
2009     881  345   233
2008     760  400   300

Now I want to create another stored procedure that calculates forecast by using a Moving average. I would like to have a parameter called NumberofDays to determine the number of periods used in the moving average. I am unsure how to use the fields

CREATE PROCEDURE Calculate Forecast
(@numberofdays INT = 2)
AS
***I know this syntax is not correct, hoping you can try and follow the logic I'm trying to get.
BEGIN
if @numberofdays = 2 and month(getdate())=1 then
Jan + Dec2008 = FebForecast else if
if @numberofdays = 3 and month(getdate())=1 then
jan + Dec2008 + Nov2008 = FebForecast.......

Or is there a better way of doing this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-15 : 13:48:55

what other fields does usagebyperiod return? does resultset involve date field also?

Vack
Aged Yak Warrior

530 Posts

Posted - 2009-05-15 : 13:57:25

The fields are item_no,YearVal and the 12 months Jan, Feb....

Here is what the usagebyperiod looks like:
ALTER PROCEDURE [dbo].[UsageCurrentYear]
AS
SELECT item_no,Year(doc_dt) as YearVal,
SUM(CASE WHEN DATEPART(mm,doc_dt)=1 THEN CASE WHEN doc_type = 'I' THEN quantity WHEN doc_type = 'R' THEN -1 * quantity END ELSE 0 END) AS January,
SUM(CASE WHEN DATEPART(mm,doc_dt)=2 THEN CASE WHEN doc_type = 'I' THEN quantity WHEN doc_type = 'R' THEN -1 * quantity END ELSE 0 END) AS Feb,
SUM(CASE WHEN DATEPART(mm,doc_dt)=3 THEN CASE WHEN doc_type = 'I' THEN quantity WHEN doc_type = 'R' THEN -1 * quantity END ELSE 0 END) AS March,
SUM(CASE WHEN DATEPART(mm,doc_dt)=4 THEN CASE WHEN doc_type = 'I' THEN quantity WHEN doc_type = 'R' THEN -1 * quantity END ELSE 0 END) AS April,
SUM(CASE WHEN DATEPART(mm,doc_dt)=5 THEN CASE WHEN doc_type = 'I' THEN quantity WHEN doc_type = 'R' THEN -1 * quantity END ELSE 0 END) AS May,
SUM(CASE WHEN DATEPART(mm,doc_dt)=6 THEN CASE WHEN doc_type = 'I' THEN quantity WHEN doc_type = 'R' THEN -1 * quantity END ELSE 0 END) AS June,
SUM(CASE WHEN DATEPART(mm,doc_dt)=7 THEN CASE WHEN doc_type = 'I' THEN quantity WHEN doc_type = 'R' THEN -1 * quantity END ELSE 0 END) AS July,
SUM(CASE WHEN DATEPART(mm,doc_dt)=8 THEN CASE WHEN doc_type = 'I' THEN quantity WHEN doc_type = 'R' THEN -1 * quantity END ELSE 0 END) AS Aug,
SUM(CASE WHEN DATEPART(mm,doc_dt)=9 THEN CASE WHEN doc_type = 'I' THEN quantity WHEN doc_type = 'R' THEN -1 * quantity END ELSE 0 END) AS Sept,
SUM(CASE WHEN DATEPART(mm,doc_dt)=10 THEN CASE WHEN doc_type = 'I' THEN quantity WHEN doc_type = 'R' THEN -1 * quantity END ELSE 0 END) AS Oct,
SUM(CASE WHEN DATEPART(mm,doc_dt)=11 THEN CASE WHEN doc_type = 'I' THEN quantity WHEN doc_type = 'R' THEN -1 * quantity END ELSE 0 END) AS Nov,
SUM(CASE WHEN DATEPART(mm,doc_dt)=12 THEN CASE WHEN doc_type = 'I' THEN quantity WHEN doc_type = 'R' THEN -1 * quantity END ELSE 0 END) AS Dec

from iminvtrx_sql
where doc_type = 'I' or (source = 'O' and doc_type = 'R') and
doc_dt>= DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)
AND doc_dt< DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)
group by item_no, year(doc_dt)
order by item_no

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-15 : 14:01:36

nope for finding moving average you need to have datefield also for moving back period based on @numberofdays parameter.

Vack
Aged Yak Warrior

530 Posts

Posted - 2009-05-15 : 14:07:47

ok. Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources