How to use aggregate functions in SQL Server

I have a situation where I have a Customer table and I need to locate document numbers from a column which is structured as a string where the first 4 characters represent the type of document (e.g.,’BILL000000000001′, ‘PYMT000000000001’). Each of these documents also has a corresponding document date. I want to fetch the CustomerNumber where the DocType is BILL with the max(DocDate) and return the CustomerNumber and the DocDate. This part is fairly straightforward and presents no challenge.

My problem is that some customers do not as yet have a document starting with ‘BILL’. For those, I want to fetch the CustomerNumber and max(DocDate) that matches another string XXXX as the first 4 characters. In essence, I need to use some sort of conditional expression that fetches either the CustomerNumber and max(DocDate) for a BILL type, or the Customer Number and max(DocDate) for a XXXX type of record. The issue is that there is a possibility that both a BILL and XXXX type might exist for a Customer. Where a BILL exists, I want to pick the CustomerNumber and max(DocDate) for that customer. However, where an XXXX type exists but no BILL type document is available, I need the CustomerNumber and max(DocDate) for type XXXX.

I was thinking of using EXCEPT and UNION but I don’t think either option really works for me. Here’s my SQL with EXCEPT, if it helps at all.

select CustomerNumber, max(DocDate)
FROM Table1
where substring(DocumentNumber,1,4) = 'BILL'
group by CustomerNumber
EXCEPT
select CustomerNumber, max(DocDate)
from Table1
where substring(umDocumentNumber,1,4) = 'MISC'
group by CustomerNumber

Answer

If I understand your logic, you need the CustomerNumber and MAX(DocDate) for customers that have a BILL type record, and you also need the CustomerNumber and MAX(DocDate) for any customers where a BILL type record does not exist.

A UNION of your initial results and another result set that includes a WHERE NOT EXISTS clause should return what you’re looking for.

SELECT 
   CustomerNumber
    ,max(DocDate)
FROM Table1
WHERE substring(DocumentNumber, 1, 4) = 'BILL'
GROUP BY CustomerNumber

UNION ALL

SELECT 
   CustomerNumber
    ,max(DocDate)
FROM Table1 AS out
WHERE 
  NOT EXISTS (SELECT 1
              FROM Table1 AS in
              WHERE 
                in.CustomerNumber = out.CustomerNumber
                AND
                substring(umDocumentNumber, 1, 4) = 'BILL'
GROUP BY CustomerNumber;