Solved

data closest to the zero

Posted on 2014-02-13
14
276 Views
Last Modified: 2014-02-14
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
0
Comment
Question by:eli411
  • 6
  • 3
  • 3
  • +1
14 Comments
 
LVL 34

Expert Comment

by:Paul MacDonald
ID: 39858921
Can you add...
     ORDER BY DISCOUNT
...and take the top result?
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39859115
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
 
LVL 32

Expert Comment

by:awking00
ID: 39859259
>>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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 40

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 100 total points
ID: 39859460
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
 
LVL 2

Author Comment

by:eli411
ID: 39859491
let me try!  Thanks for the post Kyle.
0
 
LVL 2

Author Comment

by:eli411
ID: 39859539
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
 
LVL 32

Expert Comment

by:awking00
ID: 39859608
Can you provide the data and query that would generate the two .jpg files?
0
 
LVL 34

Expert Comment

by:Paul MacDonald
ID: 39859678
Have you tried the ORDER BY?
0
 
LVL 2

Author Comment

by:eli411
ID: 39859728
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
 
LVL 2

Author Comment

by:eli411
ID: 39859798
I used order by!
0
 
LVL 40

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 100 total points
ID: 39859804
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
 
LVL 32

Accepted Solution

by:
awking00 earned 400 total points
ID: 39859871
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
 
LVL 2

Author Comment

by:eli411
ID: 39860063
let me try awking00!  Thanks for the code
0
 
LVL 2

Author Comment

by:eli411
ID: 39860361
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

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question