November 30, 2007 at 12:03 am
CREATE TABLE TEMP (A1 INT IDENTITY(1,1),Dollarcolumn decimal(10,2),PercentageColumn decimal (10,2))
INSERT INTO TEMP (Dollarcolumn,PercentageColumn)
SELECT 2,0
UNION ALL
SELECT 0,5.5
UNION ALL
SELECT 5.5,0
UNION ALL
SELECT 0,4
UNION ALL
SELECT 124,0
UNION ALL
SELECT 125.6,0
select * from temp
/*I want the output in following way.I will create a procedure that has two inputs 1>Symbol 2>ID.
Based on the Symbol if it is $ then the output should be like this
$2 for Id = 1, $5.50 for ID = 3,$124 for ID = 5,$125.60 for ID = 6.
Basically if I pass $ and a ID if the fractional part does contain only 00 then I want to eliminate it.
IF it contains any non 0 part then then I want to include it.
If it is % then the same rule applies
The ID and symbol will not be passed for that particular ID where Dollarcolumn orPercentageColumn = .00 */
CREATE PROCEDURE STP_selectColumn @symbol char(1),@ID int
AS
SET NOCOUNT ON
SELECT CASE WHEN @symbol = '$' AND substring(cast(Dollarcolumn as varchar),charindex('.',Dollarcolumn)+1,1) <> 0 then '$'+Dollarcolumn
WHEN @symbol = '$' AND substring(cast(Dollarcolumn as varchar),charindex('.',Dollarcolumn)+1,1) = 0 then '$'+left(Dollarcolumn,charindex('.',Dollarcolumn)-1)
WHEN @symbol = '%' AND substring(cast(PercentageColumn as varchar),charindex('.',PercentageColumn)+1,1) <> 0 then '%'+PercentageColumn
WHEN @symbol = '%' AND substring(cast(PercentageColumn as varchar),charindex('.',PercentageColumn)+1,1) = 0 then '%'+left(PercentageColumn,charindex('.',PercentageColumn)-1)
else NULL END
FROM TEMP WHERE a1 = @ID
SET NOCOUNT OFF
exec STP_selectColumn '$',1
--This should return $2
exec STP_selectColumn '$',6
--This should return $125.60
Drop proc STP_selectColumn
drop table temp
I am unable to get the required result, Please help me.
November 30, 2007 at 12:35 am
you'd be better off keeping the presentation issues at the presentation layer ! (So handle it in your vb app. or whatever, maybe simply even use the clientside settings in e.g. a grid)
To solve your query, you need to perform the varchar-conversion at every place where you want to mix characters and numbers !
SELECT CASE WHEN @symbol = '$' AND substring(cast(Dollarcolumn as varchar),charindex('.',Dollarcolumn)+1,1) <> 0 then '$'+cast(Dollarcolumn as varchar)
WHEN @symbol = '$' AND substring(cast(Dollarcolumn as varchar),charindex('.',Dollarcolumn)+1,1) = 0 then '$'+left(cast(Dollarcolumn as varchar),charindex('.',cast(Dollarcolumn as varchar))-1)
WHEN @symbol = '%' AND substring(cast(PercentageColumn as varchar),charindex('.',PercentageColumn)+1,1) <> 0 then '%'+cast(PercentageColumn as varchar)
WHEN @symbol = '%' AND substring(cast(PercentageColumn as varchar),charindex('.',PercentageColumn)+1,1) = 0 then '%'+left(cast(PercentageColumn as varchar),charindex('.',cast(PercentageColumn as varchar))-1)
else NULL END
FROM TEMP
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply