Pass Parameters to function - 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

Pass Parameters to function

Author

Topic

petersrj
Starting Member

20 Posts

Posted - 2011-11-01 : 17:54:43

I am trying to pass parameters from a table to the ROUND function. For example, I have two tables as follows:

Table: TodaysWeather

TimeFrame Temperature WindSpeed Humidity
11/1/2011 10:05 78.245736 10.429874 52.379882
11/1/2011 10:06 78.269854 11.743845 52.488923

Table: WeatherFormats
Channel RoundPrecision RoundFunction
Temperature 1 0
WindSpeed 2 0
Humidity 0 1

I want to return a result set of the data in the first table rounded by the data in the second table.
The SELECT statement would be something like the following; however, I want the parameters from the WeatherFormats table.

SELECT ROUND(Temperature, 1, 0),
ROUND(WindSpeed, 2, 0),
ROUND(Humidity, 0, 1)
FROM TodaysWeather

The result set would be:

TimeFrame Temperature WindSpeed Humidity
11/1/2011 10:05 78.2 10.43 52
11/1/2011 10:06 78.3 11.74 52

Any help is appreciated.

pduffin
Yak Posting Veteran

68 Posts

Posted - 2011-11-01 : 19:44:14

select
ROUND(Temperature, Temperatureroundprecision, TemperatureRoundFunction),
ROUND(WindSpeed, WindSpeedroundprecision, WindSpeedRoundFunction),
ROUND(Humidity, Humidityroundprecision, HumidityRoundFunction)
from TodaysWeather w
join
(
select
max(case when channel = 'windspeed' then roundprecision else -1 end) as WindSpeedroundprecision,
max(case when channel = 'windspeed' then RoundFunction else -1 end) as WindSpeedRoundFunction,
max(case when channel = 'Humidity' then roundprecision else -1 end) as Humidityroundprecision,
max(case when channel = 'Humidity' then RoundFunction else -1 end) as HumidityRoundFunction,
max(case when channel = 'Temperature' then roundprecision else -1 end) as Temperatureroundprecision,
max(case when channel = 'Temperature' then RoundFunction else -1 end) as TemperatureRoundFunction
from WeatherFormats
) a on 1 = 1

Likes to run, hates the runs!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-02 : 05:24:32

[code]
SELECT TimeFrame,Temperature, WindSpeed, Humidity
FROM
(
SELECT TimeFrame,Field,Val
FROM TodaysWeather t
UNPIVOT(Val FOR Field IN (Temperature, WindSpeed, Humidity))u
)t
INNER JOIN WeatherFormats w
ON w.Channel = t.Field
PIVOT(SUM(ROUND(Val,RoundPrecision,RoundFunction)) FOR Field IN (Temperature, WindSpeed, Humidity))p
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

petersrj
Starting Member

20 Posts

Posted - 2011-11-02 : 09:49:38

Thanks for the responses. I will probably go with the first response as I can set default values through the CASE ELSE combination.

Thank you again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-02 : 10:09:15

quote:
Originally posted by petersrj

Thanks for the responses. I will probably go with the first response as I can set default values through the CASE ELSE combination.

Thank you again.

even in second case also you can do that
like


SELECT TimeFrame,Temperature, WindSpeed, Humidity
FROM
(
SELECT TimeFrame,Field,Val
FROM TodaysWeather t
UNPIVOT(Val FOR Field IN (Temperature, WindSpeed, Humidity))u
)t
LEFT JOIN WeatherFormats w
ON w.Channel = t.Field
PIVOT(SUM(ROUND(Val,COALESCE(RoundPrecision,-1),COALESCE(RoundFunction,-1))) FOR Field IN (Temperature, WindSpeed, Humidity))p

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources