I need some help with an incredibly ugly DECIMAL string manipulation. The datatype for all values used in these calculations is varchar. (I know. There's nothing I can do about it.) I have two different groups of calculations -- I need one to always come back with 3 decimals, and I need the other to always come back with 2 decimals. By itself, that is not too bad... I thought I could use something like this -- CONVERT(varchar(20), CAST(SUM(value) as MONEY),1) --- where CONVERT( varchar, m, 1 ) gives me the value into a string with commas, but it's just not coming together for me.
Again, the two groups of calculations below -- I need the top one to always return 3 decimals with ROUND, and I need the bottom one to always return 2 decimals --- whether the values are whole or not.
Is anyone able to help?
Always display 3 decimal points, whether it is a whole number or not -- ie., 200.000, .015 -- ROUNDing up for anything 4 and greater. .0539 becomes .054
1. CAST(ROUND((mmmm / oooo), 2) AS FLOAT)
2. CAST(LWength AS VARCHAR(20)) + ' oz" x 1" piece'
3. CAST(WPLength AS VARCHAR(20)) + ' oz/' + MinWidth + '" x 1" piece'
For these, I just need to always display 2 decimals, whether the value is a whole number or not -- ie., 1.00, .50
1. MinWidth + ' x ' + CAST(LPWWeight AS VARCHAR(20)) + '"/oz'
2. CAST(LPWeight AS VARCHAR(20))+ ' sq in/dwt'
3. MinWidth + ' x ' + CAST(LPWeight AS VARCHAR(20)) + '"/oz'
WHEN LPWeight >= 2 THEN
CAST(LPWWeight AS VARCHAR(20)) + ' ft/oz'
CAST(LPWeight * 12 AS VARCHAR(20)) + ' in/oz' END