How do I remove this error? Thanks for your help.
SELECT RTRIM(PARTNUMBER) AS [PART NUMBER]
, CAST(SUBTOTAL_COST AS MONEY) AS [COST]
, CAST(FLAT_DOC_TOTAL AS MONEY) AS [RETAIL]
, CAST((FLAT_DOC_TOTAL-SUBTOTAL_COST)/FLAT_DOC_TOTAL AS DECIMAL(12,2)) AS [MARGIN%]
, CAST(POST_STK_DATE AS DATE) AS [COMPLETED DATE]
FROM WO_HDR
WHERE POST_STK_DATE > DATEADD(MONTH,-36,GETDATE())
Replace the denominator (FLAT_DOC_TOTAL) with NULLIF(FLAT_DOC_TOTA,0)
That instructs SQL Server to replace the value of FLAT_DOC_TOTA with a null if the value is 0. That will result in the MARGIN% column in the result being null if the denominator is zero.
, CAST((FLAT_DOC_TOTAL-SUBTOTAL_COST)/NULLIF(FLAT_DOC_TOTAL,0) AS DECIMAL(12,2)) AS [MARGIN%]
As a general rule, I always use that NULLIF function for the denominator to avoid the divide by zero error.
Thank you.