Calculate and display last 10 records average in SQL Server

Tevin

SOLVED CLOSED ACTIVE ANSWERED
User: Tevin
Posted: on Jun 08, 2021 11:03 PM
Forum: SQL Server
Answer: 1
Views: 2531

I need to write a query that takes the last 10 recordings per component, get the average of those 10 and then display as 1 row.

Table:

USE [Quality]
GO
SELECT [ComponentWeightCheckID]
      ,[EmpID]
      ,[StockCode]
      ,[Process]
      ,[PartWeightGram]
      ,[SprueWeightGram]
      ,[BomWeightKG]
      ,[TolerancePercentage]
      ,[AssetID]
      ,[CapturedDateTime]
      ,[Hostname]
      ,[Username]
      ,[IsOutOfSpec]
      ,[Tool]
      ,[Deleted]
  FROM [dbo].[tblComponentWeightCheck]
GO

Below is my query but it is not correct because it returns multiple values.

SELECT  Top 10 cwc.StockCode,i.LongDesc AS 'Description', cwc.IsOutOfSpec,cwc.CapturedDateTime, 
(SELECT  TOP 10 AVG(PartWeightGram) FROM tblComponentWeightCheck c WHERE c.StockCode = '000-256966-020' GROUP BY c.StockCode ) AS  'Part Weight Average',
(SELECT  TOP 10 AVG([SprueWeightGram]) FROM tblComponentWeightCheck c WHERE c.StockCode = '000-256966-020' GROUP BY c.StockCode  ) AS 'Sprue Weight Average',
(SELECT  TOP 10 AVG(c.TolerancePercentage) FROM tblComponentWeightCheck c WHERE c.StockCode = '000-256966-020' GROUP BY c.StockCode   ) AS 'Tolerance Average',
(SELECT  TOP 10 AVG([BomWeightKG]) FROM tblComponentWeightCheck c WHERE c.StockCode = '000-256966-020' GROUP BY c.StockCode   ) AS 'Bom Weight Average',
(SELECT  TOP 10 AVG((c.PartWeightGram + (c.SprueWeightGram / 2)) - (c.BomWeightKG * 1000)) FROM tblComponentWeightCheck c WHERE c.StockCode = '000-256966-020' GROUP BY c.StockCode   ) AS 'Variance To Syspro Average',
(SELECT  TOP 10 AVG((((((c.PartWeightGram + c.SprueWeightGram  / 2))) - (c.BomWeightKG * 1000)) / (c.BomWeightKG * 1000))  * 100) FROM tblComponentWeightCheck c WHERE c.StockCode = '000-256966-020' GROUP BY c.StockCode   ) AS 'Variance To Syspro & Average'                
FROM tblComponentWeightCheck cwc
LEFT JOIN [Mercury].[EncoreCompanyA].dbo.InvMaster i ON  cwc.StockCode = i.StockCode
WHERE cwc.StockCode = '000-256966-020'
GROUP BY cwc.StockCode, i.LongDesc, cwc.IsOutOfSpec,cwc.CapturedDateTime

Download FREE API for Word, Excel and PDF in ASP.Net

Tevin

0 6

Reply
ANSWER
User: Tevin
Replied: on Jun 09, 2021 11:31 PM
Report

SELECT MAX(CONVERT(Date, CapturedDateTime)) AS 'DateCaptured' 
,StockCode
, LongDesc
,AVG(PartWeightGram) AS 'Part Weight Average'
,AVG(SprueWeightGram) AS 'Sprue Weight Average'
,AVG(TolerancePercentage) AS 'Tolerance Average' 
,AVG(BomWeightKG * 1000) AS 'Bom Weight Average'
,AVG(PartWeightGram + ( SprueWeightGram / 2) - (BomWeightKG * 1000)) AS 'Variance To Syspro Average' 
,AVG((((((PartWeightGram + SprueWeightGram  / 2))) - (BomWeightKG * 1000)) / (BomWeightKG * 1000))  * 100) AS 'VarianceToSysproPct'
,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 'Out Of Spec Average'
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 c.StockCode='000-256576-020' And 
Deleted = 'False'
AND CONVERT(Date, CapturedDateTime) Between '2021-05-01' AND '2021-06-01'
ORDER BY CapturedDateTime DESC
) a
GROUP BY StockCode, LongDesc

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Calculate and display last 10 records average in SQL Server

Company

Explore

Follow us

Contact Us