CustomerType BillName CustomerID CurrentAmt PriorYear
CoPack       ABC Co.  1010       1000.00    0.00
CoPack       DEF Co.  1011       0.00       1000.00
CoPack       GHI Co.  1012       1000.00    100.00

 
--Showing all customers, whether they have invoices or not for the current or past year:
SELECT    CUS.CUS_BillName, CUS.CUS_CustomerID, CUS.CUS_CustomerType,
										SUM(INVCur.INV_InvoiceAmt) AS CurrentAmt, 
										SUM(INVLastYear.INV_InvoiceAmt) AS LastYearAmt
FROM         CUS left JOIN INV as INVCur ON INVCur.INV_CustomerID = CUS.CUS_CustomerID
															left JOIN INV as INVLastYear ON INVLastYear.INV_CustomerID = CUS.CUS_CustomerID
WHERE     
(CUS.CUS_CustomerType IN ('CoPack' , 'Institutional') AND 
(((INVCur.INV_ProdFam IS NOT NULL) AND 
	(INVCur.INV_InvoiceDate >= '1/1/2001') AND 
	(INVCur.INV_InvoiceDate <= '3/31/2001'))
 OR
 ((INVLastYear.INV_ProdFam IS NOT NULL) AND 
	(INVLastYear.INV_InvoiceDate >= '1/1/2001') AND 
	(INVLastYear.INV_InvoiceDate <= '3/31/2001'))
) 
GROUP BY CUS.CUS_BillName, CUS.CUS_CustomerID, CUS.CUS_CustomerType
ORDER BY CUS.CUS_CustomerType, CUS.CUS_BillName

--Showing only customers who have at least one relevant in the current or past year:
SELECT    CUS.CUS_BillName, CUS.CUS_CustomerID, CUS.CUS_CustomerType,
										SUM(INVCur.INV_InvoiceAmt) AS CurrentAmt, 
										SUM(INVLastYear.INV_InvoiceAmt) AS LastYearAmt
FROM         CUS left JOIN INV as INVCur ON INVCur.INV_CustomerID = CUS.CUS_CustomerID
															left JOIN INV as INVLastYear ON INVLastYear.INV_CustomerID = CUS.CUS_CustomerID
WHERE     
(CUS.CUS_CustomerType IN ('CoPack' , 'Institutional') AND 
(((INVCur.INV_ProdFam IS NOT NULL) AND 
	(INVCur.INV_InvoiceDate >= '1/1/2001') AND 
	(INVCur.INV_InvoiceDate <= '3/31/2001'))
 OR
 ((INVLastYear.INV_ProdFam IS NOT NULL) AND 
	(INVLastYear.INV_InvoiceDate >= '1/1/2001') AND 
	(INVLastYear.INV_InvoiceDate <= '3/31/2001'))
) AND 
	--at least one of the two years has data.
((INVCurYear IS NOT NULL) OR
	(INVLastYear IS NOT NULL))

GROUP BY CUS.CUS_BillName, CUS.CUS_CustomerID, CUS.CUS_CustomerType
ORDER BY CUS.CUS_CustomerType, CUS.CUS_BillName