Solved

data closest to the zero

Posted on 2014-02-13
14
278 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

751 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