Matching only one specific row in a JOIN where many exist

Question

(Advantage Database Server) I have a table of service providers that, for auditing purposes, are never deleted. They have a start date and end date; in the case of changes like name or address, the existing row is end dated, a new row is created, and a new start date is assigned for the changed data.

During processing of payments to those providers, I need a summary page that lists the provider name, address, identifier (ProvID), and total amount being paid. This is done in a fairly straightforward query with a SUM() and GROUP BY.

The problem appears when there are two or more rows for a specified provider identifier. I end up with duplicate rows (which could result in multiple payments to that provider if not caught).

My first thought was to use something (ugly, but performs reasonably quickly) like a subselect:

SELECT ... FROM service s
INNER JOIN provider p ON p.ProvID = s.ProvID
AND (p.EndDate IS NULL or p.EndDate = (SELECT Max(EndDate) FROM
   provider lu WHERE lu.ProvID = s.ProvID))

Unfortunately, this still ended up finding two rows; one row for the NULL EndDate and one for the MAX(EndDate).

I handle this in other cases (eg., locating the proper ProvID for a service provided on a specific date) using

p.EndDate is null or (s.ServiceDate BETWEEN p.StartDate AND p.EndDate)

Unfortunately, since the problem query is a GROUP BY with an aggregate, the service date isn't available.

Any suggestions?

EDIT: What I'm looking for is either the row with the NULL EndDate if it exists, OR the row with the Max(EndDate) if the NULL row doesn't exist. This covers the case, for instance, where a supplier was terminated yesterday, but did work last week, and we'll be paying them next week.

Answer 1

So I guess if there is a row with NULL end date, you want that one, otherwise you want the one with the largest end date?

I'm not sure about ADS, but the following would work on SQL Server:

SELECT ... FROM service s
INNER JOIN provider p ON p.ProvID = s.ProvID
AND (COALESCE(p.EndDate, '2037-01-01') = (
   SELECT Max(COALESCE(EndDate, '2037-01-01')) FROM
   provider lu WHERE lu.ProvID = s.ProvID)
)

The COALESCE operator returns the first non-null parameter, so this is basically just setting the nulls to a time far in the future, so that SELECT MAX will give you the one with the NULL end date if there is one.