Matching only one specific string in a JOIN where many exist
(Advantage Database Server) I have a table of service providers that are never dropped for audit purposes. They have a start date and an end date; for changes such as name or address, the existing line ends, a new line is created, and a new start date is assigned to the changed data.
When processing payments to these providers, I need a summary page that lists the provider name, address, ProvID and total amount. This is done in a fairly simple query using SUM () and GROUP BY.
The problem occurs if there are two or more strings for the specified provider ID. I am getting duplicate rows (this could result in multiple payments to this provider if not caught).
My first thought was to use something (ugly, but executable fast enough) as a sub-entry:
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 ended up searching for two lines anyway; one row for NULL EndDate and one for MAX (EndDate).
I handle this in other cases (for example, having 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 query of the problem is a GROUP BY with an aggregate, no service date is available.
Any suggestions?
EDIT: I'm looking for either a string with a NULL EndDate if it exists, or a string with a Max (EndDate) if the NULL string doesn't exist. This is for a case, for example, where a supplier was terminated yesterday but was in operation last week and we will pay them next week.
source to share
So I guess if there is a string with a NULL end date you want it, otherwise you want the longest end date?
I'm not sure about ADS, but the following will 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 statement returns the first non-null parameter, so this basically just sets the zeros to a time in the future, so SELECT MAX will give you a NULL ending date number, if any.
source to share
in the second condition, you should only get max if there is no NULL EndDate
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)
AND NOT EXISTS (SELECT NULL
FROM provider lu
WHERE lu.ProvID = s.ProvID
AND lu.EndDate IS NULL)
)
)
source to share
What you are talking about is a data store type 2 size.
You need to join the ID as well as the StartDate and EndDate to get the correct data.
OTTOMH code
SELECT TransactionId, TransactionType
FROM TransactionList Tx
INNER JOIN TransactionType TxType
ON Tx.TransactionTypeId = TxType.TxTypeId
AND Tx.TransactionDate Between TxType.StartDate and TxType.EndDate
source to share
What does the current date represent in your supplier table? EndDate = NULL, EndDate = Max (EndDate) or EndDate = '9999-01-01'? All three are valid choices, but it really should be unambiguous, because if they are not, you will still run into duplicate rows in queries, no matter how much you think about that particular query. So I suggest setting this on the provider table and then something like this should work:
select p.name, p.address, p.id, sum(s.amount)
from provider p
join service s on p.id=s.provider_id
where p.endDate is NULL
group by p.name, p.address, p.id
source to share