[Solved] Query not returning 10 results in SQL Server

Tevin
 
on Jun 10, 2021 06:06 AM
483 Views

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

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
Tevin
 
on Jun 11, 2021 05:19 AM

I Managed to figure it out.

SELECT  CapturedDateTime AS DateCaptured, 
       t.StockCode, ISNULL(i.LongDesc, '') AS LongDesc, 
	   -- if blnShowInKg Then
	   AVG(t.PartWeightGram) / 1000 AS 'PartWeightAverage', 
	   AVG(t.SprueWeightGram) / 1000 AS 'SprueWeightAverage', 
       AVG(t.BomWeightKG) AS 'BomWeightAverage',
       ((AVG(t.PartWeightGram) + (AVG(t.SprueWeightGram) / 2)) / 1000) - AVG(t.BomWeightKG) AS 'VarianceToSysproAverage', 
	  -- Else	   
	   AVG(t.PartWeightGram) AS 'PartWeightAverage', 
	   AVG(t.SprueWeightGram) AS 'SprueWeightAverage', 
       AVG(t.BomWeightKG * 1000) AS 'BomWeightAverage',
      (AVG(t.PartWeightGram) + (AVG(t.SprueWeightGram) / 2)) - AVG(t.BomWeightKG * 1000) AS 'VarianceToSysproAverage', 
	  --End if
	         AVG(t.TolerancePercentage) AS TolerancePercentageAverage, 
       ((AVG(t.PartWeightGram) + (AVG(t.SprueWeightGram) / 2)) - AVG(t.BomWeightKG * 1000)) / AVG(t.BomWeightKG * 1000) * 100 AS VarianceToSysproPctAverage,
       CASE WHEN ABS(((AVG(t.PartWeightGram) + (AVG(t.SprueWeightGram) / 2)) - AVG(t.BomWeightKG * 1000)) / AVG(t.BomWeightKG * 1000) * 100) > AVG(t.TolerancePercentage) 
              THEN 'True' ELSE 'False' END AS IsOutOfSpecAverage
FROM (
       SELECT RANK() OVER (PARTITION BY c1.StockCode ORDER BY c1.CapturedDateTime DESC, c1.ComponentWeightCheckID DESC) AS RecordRank,
              c1.ComponentWeightCheckID, c1.StockCode, c1.PartWeightGram, c1.SprueWeightGram, c1.BomWeightKG, c1.TolerancePercentage, c1.CapturedDateTime, c1.IsOutOfSpec
       FROM tblComponentWeightCheck c1 
       LEFT OUTER JOIN Mercury.EncoreCompanyA.dbo.BomStructure b1 ON b1.ParentPart = c1.StockCode 
       WHERE c1.Deleted = 0 
       AND b1.Route = '0' 
       AND c1.CapturedDateTime BETWEEN ISNULL(b1.StructureOnDate, c1.CapturedDateTime) AND ISNULL(b1.StructureOffDate, c1.CapturedDateTime)
       AND b1.Component = @Material 
       AND c1.StockCode IN (SELECT c2.StockCode 
              FROM tblComponentWeightCheck c2 
              LEFT OUTER JOIN Mercury.EncoreCompanyA.dbo.BomStructure b2 ON b2.ParentPart = c2.StockCode 
              WHERE c2.Deleted = 0 
              AND b2.Route = '0' 
              AND c2.CapturedDateTime BETWEEN ISNULL(b2.StructureOnDate, c2.CapturedDateTime) AND ISNULL(b2.StructureOffDate, c2.CapturedDateTime)
              AND b2.Component = @Material
			        --optional: if date range entered
              AND CONVERT(DATE, c2.CapturedDateTime) BETWEEN @FromDate AND @ToDate
              GROUP BY StockCode 
              HAVING COUNT(c2.ComponentWeightCheckID) >= 10)
       --optional: if date range entered
       AND CONVERT(DATE, c1.CapturedDateTime) BETWEEN @FromDate AND @ToDate 
) t 
LEFT OUTER JOIN Mercury.EncoreCompanyA.dbo.InvMaster i ON i.StockCode = t.StockCode 
WHERE t.RecordRank <= 10 
GROUP BY t.StockCode, ISNULL(i.LongDesc, '') 
--optional: if out of spec only selected 
HAVING CASE WHEN ABS(((AVG(t.PartWeightGram) + (AVG(t.SprueWeightGram) / 2)) - AVG(t.BomWeightKG * 1000)) / AVG(t.BomWeightKG * 1000) * 100) > AVG(t.TolerancePercentage) THEN 'True' ELSE 'False' END = 'True' 
ORDER BY StockCode