Member Avatar for Mike Bishop

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)

2020-03-10_09-55-39.png

Edited by Mike Bishop
  • 2 Contributors
  • 1 Reply
  • 189 Views
  • 1 Hour Discussion Span
  • Latest Post Latest Post by pritaeas
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.