Below is 2 queries.
1 Showing Grams and the other showing Kilograms.
The column 'VarianceToSysproGram' displays the correct value but the 'VarianceToSysproKg' does not display the correct Kg value for that field.
Please advise:
--Grams
SELECT s.StockCode ,
i.LongDesc,
STR(s.TolerancePercentage,10,2) AS 'TolerancePercentage' ,
s.CapturedDateTime ,
STR((((((s.PartWeightGram + s.SprueWeightGram / 2))) - (s.BomWeightKG * 1000)) / (s.BomWeightKG * 1000)) * 100,10,2) AS 'VarianceToSysproPct',
IsOutOfSpec,
s.PartWeightGram AS 'PartWeight',
s.SprueWeightGram AS 'SprueWeight' ,
(s.BomWeightKG * 1000) AS 'BomWeight',
(s.PartWeightGram + (s.SprueWeightGram / 2)) - (s.BomWeightKG * 1000) AS 'VarianceToSysproGram'
FROM tblComponentWeightCheck s
LEFT JOIN [Mercury].[EncoreCompanyA].dbo.InvMaster i ON s.StockCode = i.StockCode
WHERE CONVERT(Date, s.CapturedDateTime) Between '20210601 00:00:00'AND'20210605 00:00:00'
And Deleted = 'False'
ORDER BY s.CapturedDateTime, s.StockCode
--Kilograms
SELECT s.StockCode ,
i.LongDesc,
STR(s.TolerancePercentage,10,2) AS 'TolerancePercentage' ,
s.CapturedDateTime ,
STR((((((s.PartWeightGram + s.SprueWeightGram / 2))) - (s.BomWeightKG * 1000)) / (s.BomWeightKG * 1000)) * 100,10,2) AS 'VarianceToSysproPct',
IsOutOfSpec,
(s.PartWeightGram / 1000) As 'PartWeight' ,
(s.SprueWeightGram / 1000) As 'SprueWeight' ,
(s.BomWeightKG) AS 'BomWeight',
(s.PartWeightGram + (s.SprueWeightGram / 2)) - (s.BomWeightKG * 1000) / 1000 AS 'VarianceToSysproKg'
FROM tblComponentWeightCheck s
LEFT JOIN [Mercury].[EncoreCompanyA].dbo.InvMaster i ON s.StockCode = i.StockCode
WHERE CONVERT(Date, s.CapturedDateTime) Between '20210601 00:00:00'AND'20210610 00:00:00'
And Deleted = 'False'
ORDER BY s.CapturedDateTime, s.StockCode
