;With Hours(N)
AS
(
SELECT 0 
UNION ALL
SELECT N+1
FROM Hours
WHERE N+1 <=23
)
SELEC CONVERT(time,DATEADD(hh,n.N,0)) AS Hour,COALESCE([Good Quantity],0) AS [Output]
FROM Hours h
LEFT JOIN (
SELECT SUM(ip.qty_prod)AS 'Good Quantity', 
DATEPART(hh,DATEADD(HH, DATEDIFF(HOUR, 0, ip.created_at_local), 0)) as 'Hour', 
s.shift_desc AS 'Shift'
FROM item_prod ip INNER JOIN shift s ON ip.shift_id=s.shift_id 
WHERE (spare1 IS NOT NULL AND ip.good_prod=1) AND (ip.ent_id='116' AND s.shift_desc = 'A')
GROUP BY DATEPART(hh,DATEADD(HH, DATEDIFF(HOUR, 0, ip.created_at_local), 0)), s.shift_desc
)t
ON t.[Hour] = h.N
ORDER BY t.Hour