Isnull is your friend 🙂

SELECT 'ActiveProd' = Isnull((SELECT COUNT(*)

FROM #table_temp tt1

WHERE statusid = 1

AND Dateadd(MONTH, Datediff(MONTH, 0,

tt1.createddate), 0) =

Dateadd(MONTH, Datediff(MONTH, 0,

tt.createddate), 0)

GROUP BY Dateadd(MONTH, Datediff(MONTH, 0,

createddate),

0)), 0),

'InactiveProd' = Isnull((SELECT COUNT(*)

FROM #table_temp tt1

WHERE statusid = 2

AND Dateadd(MONTH, Datediff(MONTH, 0,

tt1.createddate), 0) =

Dateadd(MONTH, Datediff(MONTH, 0,

tt.createddate), 0)

GROUP BY Dateadd(MONTH, Datediff(MONTH, 0,

createddate

), 0)), 0),

'DiscontinutedProd' = Isnull((SELECT COUNT(*)

FROM #table_temp tt1

WHERE statusid = 3

AND Dateadd(MONTH, Datediff(MONTH, 0

,

tt1.createddate), 0) =

Dateadd(MONTH, Datediff(MONTH, 0,

tt.createddate), 0)

GROUP BY Dateadd(MONTH, Datediff(MONTH, 0,

createddate), 0)), 0),

( CONVERT(VARCHAR(4), Dateadd(MONTH, Datediff(MONTH, 0, createddate), 0), 100) +

CONVERT(VARCHAR(4), YEAR(Dateadd(MONTH, Datediff(MONTH, 0, createddate), 0)))

) AS prodcountdt

FROM #table_temp tt

WHERE Dateadd(MONTH, Datediff(MONTH, 0, createddate), 0) BETWEEN

Getdate() - 365 AND Getdate()

GROUP BY Dateadd(MONTH, Datediff(MONTH, 0, createddate), 0)

ORDER BY Dateadd(MONTH, Datediff(MONTH, 0, createddate), 0)