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
Output
| CapturedDateTime |
StockCode |
PartWeightAVG |
SprueWeightAVG |
BomWeightAVG |
| 2021-05-01 03:09:01.000 |
000-256227-010 |
0.329 |
0.04 |
0.349 |
| 2021-05-01 03:10:44.000 |
000-256227-020 |
0.33 |
0.04 |
0.349 |
| 2021-05-01 03:13:49.000 |
000-256955-010 |
0.103 |
0.049 |
0.129 |
| 2021-05-01 03:15:19.000 |
000-256955-020 |
0.103 |
0.049 |
0.129 |
| 2021-05-01 03:18:56.000 |
000-256480-000 |
0.48 |
0 |
0.485 |
| 2021-05-01 03:21:11.000 |
000-256803-010 |
0.079 |
0.006 |
0.084 |
| 2021-05-01 03:22:45.000 |
000-256803-020 |
0.0635 |
0.019 |
0.084 |
| 2021-05-01 03:24:36.000 |
000-256957-010 |
0.05 |
0.032 |
0.065 |
| 2021-05-01 03:26:14.000 |
000-256803-020 |
0.0635 |
0.019 |
0.084 |
| 2021-05-01 04:35:20.000 |
000-256916-010 |
0.116 |
0.09 |
0.1613 |
