Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2008
    Posts
    4

    Unanswered: Ceiling function problem

    Hello,
    I am running the following query. When (sum_of_prov-@Ded) < 0 and > -1, the “ceiling” function will make the amount = 0.
    Could you please advice should I just remove the “ceiling” function from the “amount” field or what changes
    needs to be done to take the amount because right now Amount is showing zero amount.


    DECLARE @Tax int
    DECLARE @Ded int

    SELECT @Tax =151.8600
    SELECT @Ded =0

    SELECT round(((ceiling(sign(@Tax-@Ded)/2)*((1+.09)*(@Tax-@Ded)))*1)/24,2) Amount
    FROM payhist WHERE empid = '30054' AND payperiod IN (29,30) AND clientid=32 AND payind='U1' AND type IN ('HLT1')

  2. #2
    Join Date
    Apr 2002
    Location
    Sunnyvale, CA USA
    Posts
    78
    By default everything is going to cast to an integer, so you're going to lose all your decimal places. Is that what you want?

  3. #3
    Join Date
    Apr 2008
    Posts
    4
    Can you please advise for the correct query so that I can get amount. Right now I am getting zero amount.
    Thanks.

  4. #4
    Join Date
    Aug 2007
    Posts
    17
    when you declare your variables,you define integer.
    "DECLARE @Tax int
    DECLARE @Ded int"
    It makes 'sign(@Tax-@Ded)' equal to 1 instead of 1.0,then 'sign(@Tax-@Ded)/2' equal to 0 instead of 0.5,so you get zero amout.

    please try another type,decimal ,etc.
    Last edited by marydan; 04-22-08 at 23:15.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Try using:
    Code:
    DECLARE @tax MONEY, @ded MONEY
    This might not fix your problem completely, but it ought to get you rolling.

    -PatP

  6. #6
    Join Date
    Apr 2002
    Location
    Sunnyvale, CA USA
    Posts
    78
    Try this:-

    DECLARE @Tax money
    DECLARE @Ded money

    SELECT @Tax = 151.8600
    SELECT @Ded = 0.0

    SELECT round(((ceiling(sign(@Tax-@Ded)/2.0)*((1.0+0.09)*(@Tax-@Ded)))*1.0)/24.0,2.0) Amount
    FROM payhist
    WHERE empid = '30054'
    AND payperiod IN (29,30)
    AND clientid=32
    AND payind='U1'
    AND type IN ('HLT1')

Posting Permissions

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