1. Registered User
Join Date
Apr 2008
Posts
4

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. Registered User
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. Registered User
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. Registered User
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.

Last edited by marydan; 04-22-08 at 23:15.

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

-PatP

6. Registered User
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
•