Round to two decimal places in a pivot

Question

Post reply

Round to two decimal places in a pivot

DReantil

SSC Veteran

Points: 244
More actions
November 6, 2018 at 7:28 am

#368356

Hi,
Hope I'm not posting in the wrong area. If so, I apologize. How can I get the results to two decimal places in the following please:
SELECT *
FROM
(
Select
AVG(((CASE WHEN vWorkOrders.StatusCode = 'CMPLT' THEN 1.0 ELSE 0.0 END)/1.0)*100) as [Completed], vPMTemplates.PMTemplateDescription AS [PM Description], DATENAME(mm, vWorkOrders.DateAvailable) AS [Month]
FROM vWorkOrders LEFT JOIN vWOPM ON vWorkOrders.IDWorkOrder = vWOPM.IDWorkOrder LEFT JOIN vPMTemplates ON vWOPM.IDPMTemplate = vPMTemplates.IDPMTemplate WHERE vWorkOrders.IDSegment = '3' AND vWorkOrders.TypeCode = 'PM' AND vWorkOrders.ShopDescription IS NOT NULL AND (vWorkOrders.DateAvailable BETWEEN '1/1/' + CAST(YEAR(GETDATE()) AS VARCHAR) AND '12/31/' + CAST(YEAR(GETDATE()) AS VARCHAR) + ' 23:59') GROUP BY /*vWorkOrders.WONumber,*/ vPMTemplates.PMTemplateDescription, vWorkOrders.IDWorkOrder, vWOPM.IDPMTemplate, vWOPM.IDWorkOrder, vWorkOrders.ShopDescription, vWorkOrders.DateAvailable
) AS SourceTable
Pivot
(
AVG(Completed)
FOR [Month] IN ([January], [February], [March], [April] ,[May], [June], [July], [August], [September], [October], [November], [December])) AS PivotTable;
Below is the result:
Thank you very much for any assistance.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

DaveBriCam SSCarpal Tunnel Points: 4294 More actions · Answer 1

try this : select cast(your_float_column as decimal(10,2))from your_table

DReantil SSC Veteran Points: 244 More actions · Answer 2

Thanks very much for the help but I still can't seem to get it to work.

pietlinden SSC Guru Points: 63436 More actions · Answer 3

CAST(AVG(((CASE WHEN vWorkOrders.StatusCode = 'CMPLT' THEN 1.0 ELSE 0.0 END)/1.0)*100) AS DECIMAL(10,2) as [Completed],
?

DReantil SSC Veteran Points: 244 More actions · Answer 4

I'm pretty sure I tried that, but just to be sure I tried it again and it does run but I still get the same results. Thank you for hanging in there.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

DReantil - Thursday, November 8, 2018 8:10 AM
I'm pretty sure I tried that, but just to be sure I tried it again and it does run but I still get the same results. Thank you for hanging in there.

CAST(AVG(Completed) AS DECIMAL(10,2))

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

DReantil SSC Veteran Points: 244 More actions · Answer 6

Definitely tried that one. Here is what I get:

Msg 195, Level 15, State 1, Line 41

'CAST' is not a recognized aggregate function.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

DReantil - Thursday, November 8, 2018 9:07 AM
Definitely tried that one. Here is what I get:
Msg 195, Level 15, State 1, Line 41
'CAST' is not a recognized aggregate function.

Dammit, of course. Then list your columns in the outer SELECT instead of using SELECT *, and implement the CAST there.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

DReantil SSC Veteran Points: 244 More actions · Answer 8

I understand what you mean. Actually doing it will be another thing. Wish me luck and thank you so much.