asked on

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

Paul MacDonald

Can you add...
ORDER BY DISCOUNT
...and take the top result?

Kyle Abrahams, PMP

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

awking00

>>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?

SOLUTION

Kyle Abrahams, PMP

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

eli411

ASKER

let me try! Thanks for the post Kyle.

eli411

ASKER

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.

awking00

Can you provide the data and query that would generate the two .jpg files?

Paul MacDonald

Have you tried the ORDER BY?

eli411

ASKER

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'

eli411

ASKER

I used order by!

SOLUTION

Kyle Abrahams, PMP

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

awking00

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

eli411

ASKER

let me try awking00! Thanks for the code

eli411

ASKER

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.