Calculate average of last 10 record from each group in SQL Server

Tevin
 
on Jun 06, 2021 11:44 PM
572 Views

I would like to return record for the average part weight of the last 10 records for a particular stock code from the table below:

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

This is my example but it returns multiple records.

SELECT TOP 10  StockCode , 
CapturedDateTime,
AVG(distinct s.PartWeightGram) AS 'PartWeightAVG' , 
AVG( distinct s.SprueWeightGram) AS 'SprueWeightAVG' , 
AVG( distinct s.BomWeightKG) AS 'BomWeightAVG' 
FROM tblComponentWeightCheck s
WHERE  StockCode = '000-256966-020' 
GROUP BY CapturedDateTime, StockCode

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Jun 07, 2021 07:18 AM

Hi Tevin,

Use sub query.

Refer below query.

SQL

DECLARE @tblComponent AS TABLE
(
    CapturedDateTime DATETIME,
    StockCode VARCHAR(20),
    LongDesc VARCHAR(50),
    PartWeight DECIMAL(18,11),
    SprueWeight DECIMAL(18,11),
    BomWeight DECIMAL(18,6),
    TolerancePercentage DECIMAL(18,2),
    VarianceToSysproPct DECIMAL(18,2),
    IsOutOfSpec BIT,
    VarianceToSyspro INT
)
 
INSERT INTO @tblComponent VALUES('2021-05-01 03:09:01','000-256227-010','GUIDE BRACKET LH',0.329,0.040,0.3490,5.0,0.00,0,0)
INSERT INTO @tblComponent VALUES('2021-05-01 03:10:44','000-256227-020','GUIDE BRACKET RH',0.330,0.040,0.3490,5.0,0.29,0,0)
INSERT INTO @tblComponent VALUES('2021-05-01 03:13:49','000-256955-010','REFL B TURN RCL LH',0.103,0.049,0.1290,5.0,-1.16,0,0)
INSERT INTO @tblComponent VALUES('2021-05-01 03:15:19','000-256955-020','REFL B TURN RCL RH',0.103,0.049,0.1290,5.0,-1.16,0,0)
INSERT INTO @tblComponent VALUES('2021-05-01 03:18:56','000-256480-000','GRILL RADIATOR',0.480,0.000,0.4850,5.0,-1.03,0,0)
INSERT INTO @tblComponent VALUES('2021-05-01 03:21:11','000-256803-010','GRAY ZONE CVR LH',0.079,0.006,0.0840,5.0,-2.38,0,0)
INSERT INTO @tblComponent VALUES('2021-05-01 03:22:45','000-256803-020','GRAY ZONE CVR RH',0.077,0.006,0.0840,5.0,-4.76,0,0)
INSERT INTO @tblComponent VALUES('2021-05-01 03:24:36','000-256957-010','INNER LENS',0.050,0.032,0.0650,5.0,0.76,0,0)
INSERT INTO @tblComponent VALUES('2021-05-01 03:26:14','000-256803-020','GRAY ZONE CVR RH',0.050,0.032,0.0840,5.0,-21.43,1,0)
INSERT INTO @tblComponent VALUES('2021-05-01 04:35:20','000-256916-010','EXT B LLEDHL LH',0.116,0.090,0.1613,5.0,0.19,0,0)
 
SELECT tc1.CapturedDateTime,tc1.StockCode,
(SELECT  TOP 10 AVG(PartWeight) FROM @tblComponent tc2 WHERE tc1.StockCode = tc2.StockCode) PartWeightAVG,
(SELECT  TOP 10 AVG(SprueWeight) FROM @tblComponent tc3 WHERE tc1.StockCode = tc3.StockCode) SprueWeightAVG,
(SELECT  TOP 10 AVG(BomWeight) FROM @tblComponent tc4 WHERE tc1.StockCode = tc4.StockCode) BomWeightAVG
FROM @tblComponent tc1
GROUP BY tc1.CapturedDateTime,tc1.StockCode