October 20, 2014 at 11:52 am
Hi I would like to create a table valued function using the following data:
create table #WeightedAVG
(
Segment varchar(20),
orders decimal,
calls int
);
insert into #WeightedAVG
SELECT 'L2','13','455' UNION ALL
SELECT 'L2','5','551' UNION ALL
SELECT 'L2','4','965' UNION ALL
SELECT 'L1','4','2750' UNION ALL
SELECT 'L1','11','4155' UNION ALL
SELECT 'L2','2','3121' UNION ALL
SELECT 'L2','19','9435' UNION ALL
SELECT 'L2','2','10792' UNION ALL
SELECT 'L1','11','3379' UNION ALL
SELECT 'L1','7','4763' UNION ALL
SELECT 'L1','52','26948' UNION ALL
SELECT 'L1','10','3927' UNION ALL
SELECT 'L1','14','13492' UNION ALL
SELECT 'L2','3','2296' UNION ALL
SELECT 'L2','62','9705' UNION ALL
SELECT 'L1','5','1099' UNION ALL
SELECT 'L1','10','2570' UNION ALL
SELECT 'L1','25','5540' UNION ALL
SELECT 'L1','40','25479' UNION ALL
SELECT 'L1','9','10948' UNION ALL
SELECT 'L2','79','10679' UNION ALL
SELECT 'L3','1','1828' UNION ALL
SELECT 'L1','1','28324' UNION ALL
SELECT 'L1','1','14562' UNION ALL
SELECT 'L1','26','1248' UNION ALL
SELECT 'L1','45','8875' UNION ALL
SELECT 'L2','8','3534' UNION ALL
SELECT 'L2','12','5139' UNION ALL
SELECT 'L2','2','407' UNION ALL
SELECT 'L1','89','9551'
I would like to create a function from this where I can input columns, and two numbers to get an average to output in a table ie,
CREATE FUNCTION WeightedAVG(@divisor int, @dividend int, @table varchar, @columns varchar)
returns @Result table
(
col1 varchar(25),
WeightedAVG float
)
AS
BEGIN
insert into @Result
SELECT @columns, (@divisor / @dividend) as 'WeightedAVG' from @table group by @columns
return
END
select WeightedAVG(80, 10, #WeightedAVG, Segment)
Hope this makes sense ...
October 20, 2014 at 12:01 pm
You can't do this. To be able to query different tables and columns, you need to use dynamic code. Dynamic code can be dangerous if it's not handled correctly and it's not allowed in functions.
By the way, you should try to use inline table valued functions to avoid performance problems.
October 20, 2014 at 12:24 pm
Quick question, could you describe the problem you are trying to solve?
😎
October 20, 2014 at 12:28 pm
This is an example on how to do it through dynamic code.
DECLARE @divisor int = 80,
@dividend int = 10,
@table varchar(128) = '#WeightedAVG',
@columns varchar(8000) = 'Segment'
DECLARE @SQL nvarchar(max);
--This is meant to prevent sql injection on column names.
SET @columns = STUFF((SELECT ', ' + QUOTENAME(Item)
FROM DelimitedSplit8K(@Columns, ',')
ORDER BY ItemNumber
FOR XML PATH('')), 1, 2, '');
--The code and explanation for DelimitedSplit8K can be found in here:
--http://qa.sqlservercentral.com/articles/Tally+Table/72993/
--Create the dynamic code
SET @SQL = 'SELECT ' + @columns + '
, (@divisor / @dividend) as ' + QUOTENAME(REPLACE( @table, '#', ''), '''') + '
FROM ' + QUOTENAME( @table) + '
GROUP BY ' + @columns + ';';
--This is used to debug the code
--PRINT @SQL;
--Execute a parametrized query
EXEC sp_executesql @SQL, N'@divisor int, @dividend int', @divisor, @dividend;
October 20, 2014 at 12:41 pm
Hi Erik, I would like to create a function that would give me a 'weighted average' total number * .weighting ie, silver is worth 40% of gold so silver = value_of_gold($80) * value_weighting(40%) so a simple function would look like
select * from weightAvg(80, 40)
but the data will be coming from a table so there will be additional columns, ie, city, retailer, month so the I would like to be able to run a function that will compute a table with different columns which will also need groupings ...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply