Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

data closest to the zero

Posted on 2014-02-13
14
Medium Priority
?
284 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 40

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 40

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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

722 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