April 12, 2011 at 6:53 am
Request6List the last name of all employees in department 90 together with their monthly salary rounded to two decimal places.
select last_name,department_no, round(Annual_Salary/12,2) as 'Monthly Salary'
from Employees
where Department_No='90'
April 12, 2011 at 6:56 am
What the datatype of annual salary?
Try Annual_Salary / 12.0 (the .0 cast to decimal)
April 12, 2011 at 7:00 am
INT I THINK?
April 12, 2011 at 7:01 am
sorry its : decimal (8,2)null.....
April 12, 2011 at 7:04 am
cant get it at all?
April 12, 2011 at 7:06 am
Help us help you!
Why not give us some sample data? Ideally, the data that is returning in the unwanted format, and the exact code you're running.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgApril 12, 2011 at 7:07 am
SELECT
last_name
, department_no
, ROUND(Annual_Salary / 12.0 , 2) AS 'Monthly Salary'
FROM
dbo.Employees
WHERE
Department_No = '90'
April 12, 2011 at 7:30 am
select last_name,department_no, (Annual_Salary/12) as 'Monthly Salary'
from Employees
where Department_No='90'
this will show the monthly salary but when i put in ,2 it wont do two decimal places?
ive used the round function but still no gud?
April 12, 2011 at 7:37 am
SELECT 9 / 7
UNION ALL
SELECT 9 / 7.0
UNION ALL
SELECT ROUND(9 / 7, 2)
UNION ALL
SELECT ROUND(9 / 7.0, 2)
1.000000
1.285714
1.000000
1.290000
Look at my code again, there's an IMPLICIT CAST. I use 12.0 NOT 12.
If you are seeing more than 2 zeros then you just need to wrap the code with CONVERT(DECIMAL(18,2), round...)
April 12, 2011 at 7:37 am
normanshongo2003 (4/12/2011)
select last_name,department_no, (Annual_Salary/12) as 'Monthly Salary'from Employees
where Department_No='90'
this will show the monthly salary but when i put in ,2 it wont do two decimal places?
ive used the round function but still no gud?
Ninja's code will work.
We can't help you unless you provide us with sample source data and sample (albeit incorrect output) data. :hehe:
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgApril 12, 2011 at 1:46 pm
There are a couple of things happening here that might not be too obvious.
1. implicit converstions - even though you have a value defined as NUMERIC(18,2) (or whatever) when you divide by 12.0 you get an implicit type cast. In this case sql thinks it's a NUMERIC(23,6), might actually be converting to FLOAT first, but I'm not 100% sure about that. Here is some code that you can use to show this:
DECLARE @N NUMERIC(18,2)
SET @N = 1234.34
SELECT @N / 12.0 AS NewLVal
INTO dbo.NewVal2. Round returns the same type as the value to be rounded. Since you are trying to truncate the value you might want to use the "Funtion" parameter depending on if you want to truncate or round:SELECT ROUND(12.345, 2, 1)
SELECT ROUND(12.345, 2, 0)
So, you can just CAST the entire thing to some NUMERIC value (19,2) or what have you. Or, to make sure there are no errors you can ROUND then CAST:select
last_name,
department_no,
CAST(round(Annual_Salary/12.0, 2) AS NUMERIC(18,2)) as 'Monthly Salary'
from Employees
where Department_No='90'
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply