February 15, 2018 at 10:00 am
I have a dumb question,
I have values like 0054880, 0054885 0054890 and I need values like
5488.000,5488.500,5489.000
How can I do it using a SQL statement ?
Thanks
IQ
February 15, 2018 at 10:13 am
faizsaadq - Thursday, February 15, 2018 10:00 AMI have a dumb question,
I have values like 0054880, 0054885 0054890 and I need values like
5488.000,5488.500,5489.000
How can I do it using a SQL statement ?Thanks
IQ
Dividing by 10 and casting to a proper numeric data type.
February 15, 2018 at 10:34 am
Thanks for replying back,
I am using the following Query
select original_value,CONVERT(decimal(9,3), REPLACE(LTRIM(REPLACE("original_value"/10, '0', ' ')), ' ', '0')) AS decimal
and the output I am getting is shown below
0054880 54880.000
0054880 54880.000
0054885 54885.000
0054885 54885.000
I expect an output similar to
0054880 5488.000
0054880 5488.000
0054885 5488.500
0054885 5488.500
What should I do in my query to correct this ?
February 15, 2018 at 10:43 am
I hope you don't complicate everything like that. Also, be aware of integer division as you were a victim of it.
select original_value,
CONVERT(decimal(10,3), original_value) / 10 AS decimalvalue
FROM (VALUES('0054880'), ('0054885')) x(original_value)
February 15, 2018 at 10:49 am
faizsaadq - Thursday, February 15, 2018 10:34 AMThanks for replying back,
I am using the following Query
select original_value,CONVERT(decimal(9,3), REPLACE(LTRIM(REPLACE("original_value"/10, '0', ' ')), ' ', '0')) AS decimal
and the output I am getting is shown below
0054880 54880.000
0054880 54880.000
0054885 54885.000
0054885 54885.000I expect an output similar to
0054880 5488.000
0054880 5488.000
0054885 5488.500
0054885 5488.500What should I do in my query to correct this ?
Try casting to a numeric format first then divide by 10
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 15, 2018 at 10:54 am
Thanks this worked like a charm, appreciate your help.
February 15, 2018 at 1:41 pm
Hi there,
This works perfectly except for cases where there are leading zeros as is 0002125, I would like the output to be 213.000 instead its removing the leading zeros and giving me a different output, can this be fixed in the Query above. Thanks again
February 15, 2018 at 1:47 pm
IQ1 - Thursday, February 15, 2018 1:41 PMHi there,
This works perfectly except for cases where there are leading zeros as is 0002125, I would like the output to be 213.000 instead its removing the leading zeros and giving me a different output, can this be fixed in the Query above. Thanks again
What's the logic for rounding this value but not the others?
February 15, 2018 at 1:56 pm
The logic is the same for all except that when there are leading zeros , it does not seem to work
February 15, 2018 at 2:02 pm
IQ1 - Thursday, February 15, 2018 1:56 PMThe logic is the same for all except that when there are leading zeros , it does not seem to work
No the logic isn't the same. By the examples given 002125 should produce 212.500 not 213.000. Why are you expecting it to produce 213.000 (rounding) instead of 212.500 (not rounding)?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 15, 2018 at 3:28 pm
yes actually my bad the rounding logic is to be applied to all cases but as you can see when there are leading zeros, the output is very different.
February 16, 2018 at 4:52 am
IQ1 - Thursday, February 15, 2018 3:28 PMyes actually my bad the rounding logic is to be applied to all cases but as you can see when there are leading zeros, the output is very different.
If you cast to a numeric data type, then divide by 10 and round up, the leading zeroes will have no effect (they're ignored during the cast).
So what query do you have where the leading zeroes are breaking things?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 16, 2018 at 7:32 am
IQ1 - Thursday, February 15, 2018 3:28 PMyes actually my bad the rounding logic is to be applied to all cases but as you can see when there are leading zeros, the output is very different.
You've never posted actual output, so we cannot see the output, let alone notice differences.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 16, 2018 at 7:53 am
Depending on the actual requirements, something like this could work...
IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL
BEGIN DROP TABLE #TestData; END;
CREATE TABLE #TestData (
char_num CHAR(7) NOT NULL
);
INSERT #TestData(char_num) VALUES
('0054880'), ('0054885'), ('0054890');
--=================================================
SELECT
td.char_num,
decimal_convert = ROUND(CONVERT(DECIMAL(9,3), STUFF(td.char_num, 7, 0, '.')), 0)
FROM
#TestData td;
Results:
char_num decimal_convert
-------- ---------------
0054880 5488.000
0054885 5489.000
0054890 5489.000
0002125 213.000
February 16, 2018 at 9:20 am
SELECT original_value,
CAST(ROUND(CONVERT(INT, original_value) / 10.0, 0) AS DECIMAL(9, 3)) AS decimalvalue
FROM (VALUES('0054880'), ('0054885'), ('02120'), ('00002124'), ('2125')) x(original_value)
;
Results:original_value decimalvalue
0054880 5488.000
0054885 5489.000
02120 212.000
00002124 212.000
2125 213.000
My question would be why do you need the 3 trailing zeroes if you are rounding up?
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply