chindilog (8/31/2015)
The total quantity(sum(woitem.qtytarget) as total) result set is showing more than 6 numbers after the decimal point (example:442.2565485). How can I limit it to 2. (example:442.25)select wo.num, sysuser.username,sum(woitem.qtytarget) as total
from woitem
join wo
ON wo.id = woitem.woid
Join moitem
on moitem.id = woitem.moitemid
Join mo
ON mo.id = moitem.moid
LEFT JOIN SYSUSER ON mo.userid = sysuser.id
group by sysuser.username, num
ORDER BY CAST( REPLACE( wo.num, ':', '.') as decimal(12, 3))
Just simply change your query to something like the following in order to convert it to a data type with only two decimals. Ensure that you make it big enough to hold the highest value that you would see from the sum.
select wo.num, sysuser.username,CONVERT(DECIMAL(12,2), sum(woitem.qtytarget)) as total
from woitem
join wo
ON wo.id = woitem.woid
Join moitem
on moitem.id = woitem.moitemid
Join mo
ON mo.id = moitem.moid
LEFT JOIN SYSUSER ON mo.userid = sysuser.id
group by sysuser.username, num
ORDER BY CAST( REPLACE( wo.num, ':', '.') as decimal(12, 3))
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes