[Solved] Query not returning 10 results in SQL Server

Last Reply one month ago By Tevin

Posted one month ago

The query below does not return 10 rows at it should please assist.

 SELECT StockCode, LongDesc
 ,AVG(PartWeightGram / 1000) AS 'PartWeightAverage'
 ,AVG(SprueWeightGram / 1000) AS 'SprueWeightAverage'
 ,AVG(TolerancePercentage / 1000) AS 'TolerancePercentageAverage' 
 ,AVG((BomWeightKG * 1000) / 1000) AS 'BomWeightAverage'
 ,AVG((PartWeightGram + ( SprueWeightGram / 2) - BomWeightKG ) / 1000) AS 'VarianceToSysproAverage'
 ,AVG(((((((PartWeightGram + SprueWeightGram  / 2))) - (BomWeightKG * 1000)) / (BomWeightKG * 1000))  * 100)/ 1000) AS 'VarianceToSysproPctAverage'
 ,MAX(CONVERT(Date, CapturedDateTime)) AS 'DateCaptured'
 ,Case
 WHEN ABS(AVG((((((PartWeightGram + SprueWeightGram  / 2))) - (BomWeightKG * 1000)) / (BomWeightKG * 1000))  * 100 )) > 5 Then 'True'
 WHEN ABS(AVG((((((PartWeightGram + SprueWeightGram  / 2))) - (BomWeightKG * 1000)) / (BomWeightKG * 1000))  * 100 )) < 5 Then 'False'
 Else 'False'
 End AS 'IsOutOfSpecAverage'
 FROM
 (
 SELECT TOP 10 c.CapturedDateTime, c.StockCode,i.LongDesc
 ,(c.PartWeightGram)
 ,(c.SprueWeightGram)
 ,(c.TolerancePercentage)
 ,(c.BomWeightKG)
 ,(c.IsOutOfSpec)
 FROM tblComponentWeightCheck c 
 LEFT JOIN [Mercury].[EncoreCompanyA].dbo.InvMaster i ON  c.StockCode = i.StockCode
 WHERE Deleted = 'False' 
 AND CONVERT(Date, CapturedDateTime) Between '20210501 00:00:00' AND '20210601 00:00:00'
 ORDER BY CapturedDateTime DESC
 ) a
 GROUP BY StockCode, LongDesc

 

This question does not have replies that have been liked.