How can I do this in SQL?
-
I want to select from a table (like the one below) and I want a measure_value in return.
feet.....inches.....measure_value
...1.......11.......-30
...2.......02........10
...2.......03........20
I will be using feet and inches in my where clause. HOWEVER, the feet and inches in my where clause will not always be in the table. For example, if I select where feet = 2 and inches = 0, I won't find a match. So what I want to do is get the lower value's feet and inches and the higher value's feet and inches and do an average to come up with a measure_value.
Also, if I use a range in the where close that is "off the charts", I want to use the next closest value. For example:
feet.....inches.....measure_value
...1.......11.......-30
...2.......02........10
...2.......03........20
If I use 1 feet 10 inches in my where clause, I want the measure_value of -30.
If I use 2 feet 04 inches in my where clause, I want the measure_value of -20.
-
I'm not sure I can manage all that in a single query, however a stored procedure would be straightforward enough: use statements like select top 1 where <, select top 1 where >, to find the lower and upper bracker, if they are the same return that, if one or both ends of the bracket is/are null then whatever, else take an average. Bit more work if you want a weighted average.
By the way, can I suggest that you give your table a single 'Length' (or whatever it is measuring) column, rather than one for feet and one for inches?
-
hey,
i think you are looking for something like this:
-- [BEGIN CODE]
CREATE FUNCTION dbo.fn_to_inches(@feet AS INTEGER, @inches AS INTEGER) RETURNS INTEGER
AS
BEGIN
DECLARE @result AS INTEGER
SET @result = @feet * 12 + @inches
RETURN @result
END
CREATE FUNCTION dbo.fn_get_measure(@feet AS INTEGER, @inches AS INTEGER) RETURNS FLOAT
AS
BEGIN
SET NOCOUNT ON
DECLARE @inches AS INTEGER
SET @inches = dbo.fn_to_inches(@feet, @inches)
DECLARE @potential_measures TABLE (
measure INTEGER,
diff INTEGER
)
-- getting all measure values necessary to compute "final" measure
INSERT INTO @potential_measures (measure, diff)
SELECT
k.measure, diff = dbo.fn_to_inches(k.feet, k.inches) - @inches
FROM
some_table k
WHERE
k.feet = @feet
DECLARE @measure_hi AS INTEGER, @measure_low AS INTEGER, @measure AS FLOAT,
-- searching for an "exact" measure
SELECT @measure = p.measure FROM @potential_measures p WHERE p.diff = 0
IF ( @measure IS NULL )
BEGIN
SELECT @measure_hi = p.measure FROM @potential_measures p WHERE p.diff IN (SELECT MIN(z.diff) FROM @potential_measures z WHERE z.diff > 0)
SELECT @measure_low = p.measure FROM @potential_measures p WHERE p.diff IN (SELECT MAX(z.diff) FROM @potential_measures z WHERE z.diff < 0)
SET @measure = CASE
WHEN @measure_hi IS NOT NULL AND @measure_low IS NULL THEN @measure_hi
WHEN @measure_hi IS NULL AND @measure_low IS NOT NULL THEN @measure_low
ELSE ( @measure_hi + @measure_low ) / 2.0
END
END
RETURN @measure
END
-- [END] Code
TO GET THE MEASURE RESULT YOU WOULD SIMPLY DO THIS:
SELECT dbo.fn_get_measure(2,0)
The code assumes the following:
1) feet and inches are some type of integer,
2) @feet IS NULL and/or @inches IS NULL are not valid parameter values (i guess you can add a check for this if you want to)
that's about it i think ...
JP
-
Whoa JP,
don't know what to say that was a bunch of code! ... 🙂
Here is a different approach....
We create 2 resultsets, lower and higher and take the average.
To overcome the complications created by having the feet & inches in 2 columns we do some math to get the length in just inches.
It is better to store the length in just one column and then when you present it you can present it as feet, inches, cms, meters, gooblidoks or whatever.
In this case storing just the inches as int might be sufficient.
eg.
declare @height_in_inches int
set @height_in_inches = 73
select @height_in_inches as height_in_inches, @height_in_inches/12 as feet, @height_in_inches%12 as inches
Ok.. here is proposed solution...
create table #measures( feet int, inches int, measure int )
insert #measures values(1,11,-30)
insert #measures values(2,2,10)
insert #measures values(2,3,20)
declare @feet int, @inches int
-- example 1
select @feet = 1, @inches = 10
select @feet as feet, @inches as inches, Avg(measure) as measure from(
select * from
( select top 1 measure from #measures where ( 12 * feet + inches ) = ( 12 * @feet + @inches ) order by ( 12 * feet + inches ) asc ) as b
) t
-- example 2
select @feet = 2, @inches = 1
select @feet as feet, @inches as inches, Avg(measure) as measure from(
select * from
( select top 1 measure from #measures where ( 12 * feet + inches ) = ( 12 * @feet + @inches ) order by ( 12 * feet + inches ) asc ) as b
) t
/rockmoose
You must unlearn what You have learnt -
Hey, just a heads up ... I modified the T-SQL code in my previous post (i.e. the post that creates the fn_get_measure(..) function) based on rockmoose's recommendation to convert feet to inches.
I also overhauled the original implementation for fn_get_measure(..) so it can handle the "closest measure" case mentioned in the first post entered for this thread.
Refer to my previous post for the modified code.
Note:
The fn_add_substract_inch(..) function I originally posted is no longer needed after the code changes are.
I think the code should now work for all cases ... Keeping my fingers crossed ...
JP
-
And the code JP ?...
keeping it on your hard disk just for fun ? 😉
"I think the code should now work for all cases ... Keeping my fingers crossed..."
Test all the cases 😉
I admire your energy and commitment to Rob's question !
*/You must unlearn what You have learnt/*
--/rockmoose
You must unlearn what You have learnt
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply