Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2014
    Posts
    291
    Provided Answers: 7

    Answered: Percentage Formula in SQL

    Code:
    ROUND(A1.CONTRACT_PKG_PRICE * (1-ISNULL(I.DISCOUNT,0)),2)
    A1.CONTRACT_PKG_PRICE is a decimal data type (18,2) I.DISCOUNT is a decimal data type (8,6)

    If I hard code the percentage into the I.DISCOUNT field I get the intended results, however if I don't I get the A1.CONTRACT_PKG_PRICE amount.

    Any thoughts?

  2. Best Answer
    Posted by MCrowley

    "The formula seems to work out for me. You have the nulls covered well enough:
    Code:
    create table #temp
    (CONTRACT_PKG_PRICE decimal(18, 2),
     discount decimal (8, 6))
    
    insert into #temp values 
    	(1000.00, 0.2), -- 20% off
    	(2000.00, null), --Nothing off
    	(3000.00, 1.0), -- Free
    	(4000, 0.0), -- nothing off again
    	(null, null), -- Nothing at no discount
    	(null, .5) -- 50% off of nothing
    
    select ROUND(CONTRACT_PKG_PRICE * (1-ISNULL(DISCOUNT,0)),2)
    from #temp
    What is the result you are getting?"


  3. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,862
    Provided Answers: 17
    The formula seems to work out for me. You have the nulls covered well enough:
    Code:
    create table #temp
    (CONTRACT_PKG_PRICE decimal(18, 2),
     discount decimal (8, 6))
    
    insert into #temp values 
    	(1000.00, 0.2), -- 20% off
    	(2000.00, null), --Nothing off
    	(3000.00, 1.0), -- Free
    	(4000, 0.0), -- nothing off again
    	(null, null), -- Nothing at no discount
    	(null, .5) -- 50% off of nothing
    
    select ROUND(CONTRACT_PKG_PRICE * (1-ISNULL(DISCOUNT,0)),2)
    from #temp
    What is the result you are getting?

  4. #3
    Join Date
    Oct 2014
    Posts
    291
    Provided Answers: 7
    Contract Pkg Price * 1 not the discount. I'm wondering if it has something to do with my join. I'll follow up, thanks for replying MCrowley at least I am know I'm not completely going crazy.

  5. #4
    Join Date
    Oct 2014
    Posts
    291
    Provided Answers: 7
    It was my join, sorry I've been working on a view that drives our catalog and it has a lot of tables. I'm refactoring the code to speed up the performance. Thanks again for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •