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;