Convert Kilograms to Grams in SQL Server

Tevin
 
on Jun 10, 2021 01:14 AM
394 Views

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

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Jun 10, 2021 01:15 AM

Hi Tevin,

Refer below query.

SQL

 --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) 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