November 6, 2018 at 7:28 am
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.
November 6, 2018 at 8:03 am
try this : select cast(your_float_column as decimal(10,2))from your_table
November 8, 2018 at 6:10 am
Thanks very much for the help but I still can't seem to get it to work.
November 8, 2018 at 8:03 am
CAST(AVG(((CASE WHEN vWorkOrders.StatusCode = 'CMPLT' THEN 1.0 ELSE 0.0 END)/1.0)*100) AS DECIMAL(10,2) as [Completed],
?
November 8, 2018 at 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.
November 8, 2018 at 8:31 am
DReantil - Thursday, November 8, 2018 8:10 AMI'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))
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
November 8, 2018 at 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.
November 8, 2018 at 9:13 am
DReantil - Thursday, November 8, 2018 9:07 AMDefinitely 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.
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
November 8, 2018 at 9:47 am
I understand what you mean. Actually doing it will be another thing. Wish me luck and thank you so much.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply