Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

data closest to the zero

Posted on 2014-02-13
14
Medium Priority
?
288 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 41

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 41

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 400 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 41

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 400 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 1600 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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

876 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