I have a nested query that is counting how many invoices have been raised for a customer between a daterange which is working fine EXCEPT..... I am getting null in the Invoice_Count field and I would like to change this to 0.
I have tried isnull(SUM(CASE WHEN Inv1.ih_credit = 1 THEN - 1 ELSE 1 END),0) but no joy.
can anyone point me in the right firection?
SELECT dbo.cost_centre.coc_description, dbo.customer_detail.cd_ow_account, (SELECT SUM(CASE WHEN Inv1.ih_credit = 1 THEN - 1 ELSE 1 END) AS Count FROM dbo.invoice_header AS Inv1 WHERE (ih_datetime BETWEEN @datefrom AND @dateto) AND (ih_cd_id = dbo.customer_detail.cd_id) GROUP BY ih_cd_id) AS Invoice_Count FROM dbo.customer_detail INNER JOIN dbo.customer_setting ON dbo.customer_detail.cd_id = dbo.customer_setting.cset_customer_id INNER JOIN dbo.cost_centre ON dbo.customer_setting.cset_cost_centre_id = dbo.cost_centre.coc_id INNER JOIN dbo.customer_profile ON dbo.customer_detail.cd_id = dbo.customer_profile.cp_customer_id WHERE (NOT (dbo.customer_detail.cd_id IN (2, 3, 4, 5, 6, 7, 8))) AND (dbo.customer_profile.cp_status_id = 3)