SELECT a.ImportAlertID, a.ClientID,
		
	--IF @type = 1 then use this select
	(SELECT COUNT(*) 
		FROM invoicenotadvanced WHERE NAStatus = 0 
		AND a.ImportAlertID = ImportAlertID) AS numNA0,

	--IF @type = 2 then use this select
	(SELECT COUNT(*) 
		FROM customer WHERE NAStatus = 'Not Sent' 
		AND a.ImportAlertID = ImportAlertID) AS numNA0,

	a.CompleteStatus, a.CompletedBy		

FROM ImportAlert a


INNER JOIN ImportType b ON a.ImportTypeID = b.ImportTypeID
INNER JOIN Client c ON a.ClientID = c.pk_id

INNER JOIN (SELECT ImportAlertID, COUNT(*) AS numCustomers
	FROM customer GROUP BY ImportAlertID) cust ON a.ImportAlertID = cust.ImportAlertID

INNER JOIN (SELECT ImportAlertID, COUNT(*) AS numInvoices, SUM(invorigamt) AS sumInvoices					 
	FROM dbo.invoicenotadvanced 
	GROUP BY ImportAlertID) inv 
	ON a.ImportAlertID = inv.ImportAlertID

quote:

---

Originally posted by Zath

How can this be done?
A case statement won't work here.

---

SELECT		a.ImportAlertID,
		a.ClientID,
		CASE @type
			WHEN 1 THEN inv.numNA0
			WHEN 2 THEN cust.numNA0
		END AS numNA0,
		a.CompleteStatus,
		a.CompletedBy		
FROM		ImportAlert AS a
INNER JOIN	ImportType AS b ON a.ImportTypeID = b.ImportTypeID
INNER JOIN	Client AS c ON a.ClientID = c.pk_id
INNER JOIN	(
			SELECT		ImportAlertID,
					COUNT(*) AS numCustomers,
					SUM(CASE WHEN NAStatus = 0 THEN 1 ELSE 0 END) AS numNA0
			FROM		customer
			GROUP BY	ImportAlertID
		) as cust ON a.ImportAlertID = cust.ImportAlertID
INNER JOIN	(
			SELECT		ImportAlertID,
					COUNT(*) AS numInvoices,
					SUM(invorigamt) AS sumInvoices,
					SUM(CASE WHEN NAStatus = 'Not Sent' THEN 1 ELSE 0 END) AS numNA0
			FROM		dbo.invoicenotadvanced 
			GROUP BY	ImportAlertID
		) as inv ON a.ImportAlertID = inv.ImportAlertID