March 26, 2011 at 3:36 am
Hi Experts,
I have a table and on top of that I am running a SQL query to get the average calculations. The SQL query working fine without any issues,
The requirement is I would like this SQL Query to be converted as SQL Stored procedures so that I can run this in a easy way on multiple tables.
Step 1:-
---SQL Table Structure:
USE [abc]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[IC_Raw_In](
[I_Date] [varchar](50) NULL,
[I_O_P] [money] NULL,
[I_O_H] [money] NULL,
[I_O_L] [money] NULL,
[I_C_O] [money] NULL,
[I_Serial] [numeric](18, 0) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Step2:-
---SQL Query for Average:
WITH RankedPrices
AS
(SELECT i_serial , I_C_O, ROW_NUMBER() OVER (ORDER BY i_serial) AS rn
FROM IC_Raw_In)
SELECT a.i_serial, AVG(b.I_C_O) AS avgs
FROM RankedPrices AS a LEFT JOIN
RankedPrices AS b ON b.rn BETWEEN a.rn-11 AND a.rn
GROUP BY a.i_serial
Step3:-- Looking for help to create based on Step 2 Query as a Stored procedures to run on step1 and get the output...
Thanks in Advance...
trying to learn SQL Query World
March 26, 2011 at 10:02 am
To create a proc:
CREATE PROCEDURE p_Rank
AS
WITH RankedPrices
AS
(SELECT i_serial , I_C_O, ROW_NUMBER() OVER (ORDER BY i_serial) AS rn
FROM IC_Raw_In)
SELECT a.i_serial, AVG(b.I_C_O) AS avgs
FROM RankedPrices AS a LEFT JOIN
RankedPrices AS b ON b.rn BETWEEN a.rn-11 AND a.rn
GROUP BY a.i_serial
GO
To execute the procedure use:
EXEC p_Rank
See details for creating a stored procedure, using parameters, etc. here: http://msdn.microsoft.com/en-us/library/ms187926.aspx
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply