SQL Query for Creation of Function

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 2008 Forums

Transact-SQL (2008)

SQL Query for Creation of Function

Author

Topic

best_boy26
Starting Member

42 Posts

Posted - 2011-03-26 : 05:22:57

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 problem is I would like this SQL Query to be converted as SQL Function 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 function

Thanks,
JJ

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-26 : 09:04:29

You'd have to write it as a CLR Aggregate function in order to make it generic for any table and column. CLR Aggregates are documented in Books Online, and SQL Server includes sample code as an additional install.

best_boy26
Starting Member

42 Posts

Posted - 2011-03-26 : 12:33:33

:) Not sure now what i have to do... :)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-26 : 13:13:17

CLR aggregates are not too hard to write, especially if you hack one of the samples, you just can't write them with SSMS. Any version of Visual Studio, including Business Intelligence, should suffice.

Sliding/windowed aggregates are atypical for relational data because they don't occur over a set of data, but an ordered (and changing) subset. This kind of thing is better done in reporting software, not SQL. I'm not sure about sliding aggregates, but they all do running aggregates pretty easily. (that's pretty much why native SQL doesn't include these)

If that's not an option, there's not much else you can do I'm afraid. There's nothing wrong with the technique you're using, it's just difficult to generalize into a function. Even a CLR aggregate will be tough because of the sliding window. I'm pretty sure you can find a 3rd party library that has custom aggregate functions that might help. You could check out another technique here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=158652

But I'm not sure it will work any better. Anything other than a CLR function will also have pretty dismal performance even for small (< 10000) rowsets.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources