data closest to the zero

I have this situation where I work and I needed to get a row "Discount" data closest to the zero.  For an example in the attached image files I had ProdCode for CF15 and CF30.  

In the CF15 (ProdCode) image I am able to get the "Discount"  with value 0.375 and use that for calculation.  

The problem I had is that when I had Discount value for CF30 (ProdCode), I had both "Discount" column with (0.5 and -0.5) are closest to the Zero and the stored procedure use the data with Discount (0.5) one.  I wanted to use the Discount(-0.5) one instead.

here are my stored procedure:
USE [Rates]
GO
/****** Object:  StoredProcedure [dbo].[sp_get_real_estate_rate_old]    Script Date: 02/13/2014 16:46:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:            <Author,,Name>
-- Create date: <Create Date,,>
-- Description:      <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_get_real_estate_rate_old]
      -- Add the parameters for the stored procedure here
      @ProdCode nvarchar(10)

AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
      
            DECLARE @disc nvarchar(10)
            Set @disc = (SELECT MIN(ABS(Discount)-0) AS Discount
            FROM tblRealEstateRates
            WHERE ProdCode = @ProdCode
            AND Channel = 'RatesDefault')


            IF((SELECT COUNT(Discount) as counter
                  FROM tblRealEstateRates
                  WHERE ABS(Discount) IN
                        (SELECT MIN(ABS(Discount)-0) AS Discount
                              FROM tblRealEstateRates
                              WHERE ProdCode = @ProdCode
                              AND Channel='RatesDefault')
                  AND ProdCode = @ProdCode
                  AND Channel = 'RatesDefault') > 1)
            BEGIN
                        SELECT *
                        FROM tblRealEstateRates
                        WHERE ABS(Discount) IN
                        (SELECT MIN(ABS(Discount)-0) AS Discount
                              FROM tblRealEstateRates
                              WHERE ProdCode = @ProdCode)
                        AND ProdCode = @ProdCode AND Discount > 0
                        AND Channel = 'RatesDefault';
            END
            ELSE
                        SELECT *
                        FROM tblRealEstateRates
                        WHERE ABS(Discount) IN
                        (SELECT MIN(ABS(Discount)-0) AS Discount
                              FROM tblRealEstateRates
                              WHERE ProdCode = @ProdCode)
                        AND ProdCode = @ProdCode
                        AND Channel = 'RatesDefault';      


END
ProductTable.jpg
ProductTable2.jpg
LVL 2
eli411Asked:
Who is Participating?
 
awking00Connect With a Mentor Commented:
The following query produces these results -
PROD CHANNEL            RATE   DISCOUNT CEILING QUAL_RATE INDEX_VAL
---- --------------- ------- ---------- ------- --------- ---------
CF15 RatesDefault      3.500       .375       0         0         0
CF30 RatesDefault      4.625        -.5       0         0         0

SELECT prodcode, channel, rate, discount, ceiling, qual_rate, index_val from
(SELECT prodcode, channel, rate, discount, ceiling, qual_rate, index_val,
 row_number() over (partition by prodcode, abs(discount) order by discount) discount_row,
 row_number() over (partition by prodcode order by abs(discount)) abs_row
 FROM tblRealEstateRates
 WHERE ProdCode in ('CF30','CF15')
 AND Channel = 'RatesDefault')
where discount_row = 1 and abs_row = 1;

Does that help?
0
 
Paul MacDonaldDirector, Information SystemsCommented:
Can you add...
     ORDER BY DISCOUNT
...and take the top result?
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
use absolute value and take the min value.  


select -0.005 a into #temp
insert into #temp select .5
insert into #temp select .25
insert into #temp select -.5
insert into #temp select -0.01

select min(abs(a)) from #temp
0
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
awking00Commented:
>>I wanted to use the Discount(-0.5) one instead.<<
Is that because -0.5 is less than 0.5 or some other reason like the rate associated with -0.5 is greater than the rate associated with 0.5?
0
 
Kyle AbrahamsConnect With a Mentor Senior .Net DeveloperCommented:
Minor update:
I saw you were doing the min abs already . . . but that won't tell you the actual value.  

select -0.005 a into #temp
insert into #temp select .5
insert into #temp select .25
insert into #temp select -.5
insert into #temp select -0.01
insert into #temp select 0.005

-- use this when setting @disc
-- a = column #temp = table

select top 1 a from #temp
where abs(a) in
(select min(abs(a))from #temp)
order by a
0
 
eli411Author Commented:
let me try!  Thanks for the post Kyle.
0
 
eli411Author Commented:
The logic is like this:

Evaluating Discount both + or - and find out the one "Discount" closest to the Zero.

If "Discount" + and - one are tied (-0.5 and 0.5 in this case for ProdCode  CF30), use the minus "Discount" for calculation!  The stored procedure I had has encounter problem when Discount are tied.
0
 
awking00Commented:
Can you provide the data and query that would generate the two .jpg files?
0
 
Paul MacDonaldDirector, Information SystemsCommented:
Have you tried the ORDER BY?
0
 
eli411Author Commented:
The Data I provided in the screen are sample data from my table

syntax

select * from tblRealestateRate where ProdCode = 'CF30'

and

select * from tblRealestateRate where ProdCode = 'CF15'
0
 
eli411Author Commented:
I used order by!
0
 
Kyle AbrahamsConnect With a Mentor Senior .Net DeveloperCommented:
this should be your query:
Set @disc =
(select top 1 discount from tblRealestateRate where abs(discount) in
(select min(abs(discount)) from tblRealestateRate )
order by discount)


@paulmacd the order by in and of itself won't help him because -1 < -.05  and he needs the closest value to 0.
0
 
eli411Author Commented:
let me try awking00!  Thanks for the code
0
 
eli411Author Commented:
Guys thanks for the help!  awking's solution work!  I created  view first

view_Real_Estate_Filter

SELECT     prodcode, channel, rate, discount, ceiling, qual_rate, index_val, row_number() OVER (partition BY prodcode, abs(discount)
ORDER BY discount) AS discount_row, row_number() OVER (partition BY prodcode
ORDER BY abs(discount)) AS abs_row
FROM         tblRealEstateRates
WHERE     Channel = 'RatesDefault')

then

run a query   -- for CF30

select * from
view_Real_Estate_Filter
where (discount_Row = 1 and abs_row = 1) and (ProdCode = 'CF30')

so all I needed to do just create a stored procedure and change the
query
select * from
view_Real_Estate_Filter
where (discount_Row = 1 and abs_row = 1) and (ProdCode = 'CF30')

and the ProdCode parameter.
0
All Courses

From novice to tech pro — start learning today.