Hi Team
I want to write this query and getting this sql exception for using Error converting data type nvarchar to numeric. Where can improve this query?
WITH PremixCalculation AS ( SELECT Adr_Batch_Log.MatDesc, Adr_Batch_Log.MatCode, Adr_Batch_Log.Shift, Adr_Batch_Log.Batched, CASE WHEN Adr_Batch_Log.MatCode LIKE 'MR%' THEN 1 * COALESCE(CAST(NULLIF(Adr_Batch_Log.MatUnit, '') AS DECIMAL(18, 3)), 0) * Adr_Batch_Log.Shift * Adr_Batch_Log.Batched WHEN Adr_Batch_Log.MatCode LIKE 'RB%' THEN 25 * COALESCE(CAST(NULLIF(Adr_Batch_Log.MatUnit, '') AS DECIMAL(18, 3)), 0) * Adr_Batch_Log.Shift * Adr_Batch_Log.Batched ELSE 0 END AS Premix FROM Adroit_Batch_Suite.dbo.Adr_Batch_Log Adr_Batch_Log WHERE Adr_Batch_Log.dt >= '2024-06-01' AND Adr_Batch_Log.dt < '2024-06-02' AND (Adr_Batch_Log.MatCode LIKE 'MR%' OR Adr_Batch_Log.MatCode LIKE 'RB%') ) SELECT MatDesc, SUM(Premix) AS TotalPremix FROM PremixCalculation GROUP BY MatDesc;