CODEWITHSUNDEEP
sqlsql-serverselectconditional-statementsaggregate-functions
Jurasick
Jurasick

Reputation: 55

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

Upvotes: 0

Views: 71

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can do what you want using aggregation:

select CustomerNumber,
       coalesce(max(case when DocumentNumber like 'BILL%' then DocDate end),
                max(DocumentNumber)
               )
from Table1
where DocumentNumber like 'BILL%' or DocumentNumber like 'MISC%'
group by CustomerNumber;

Or using window functions:

select t1.*
from (select t1.*,
             row_number() over (partition by CustomerNumber
                                order by left(DocumentNumber, 4), DocDate desc
                               ) as seqnum
      from Table1 t1
     ) t1
where seqnum = 1;

This method allows you to return the entire row for the maximum document.

Upvotes: 3

Eric Brandt
Eric Brandt

Reputation: 8101

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;

Upvotes: 2

Related Questions