Keep only nearest future date from list of duplicates

[jjohnsonucd]

I am a very novice Access user. I have a table that contains all of our callback dates for our customers. The problem is that some customers have multiple callback dates. Some of them are even in the past and haven't been deleted. Some only have callback dates in the past. I want to create a query that will keep only one callback date for each customer. I would like to keep the callback date that is closest to today in the future. However I don't want to lose callback dates that are in the past if that is the only date we have for that customer. Each customer is identified by a unique Client ID field. That would be what I would use to identify duplicates. I hope this makes sense. Any help is greatly appreciated.

[cactusdata]

You can use two subqueries:

Select 
    CustomerId, 
    Nz(
        (Select Min(CallbackDate) AS MinCallbackDate
        From CustomerCallback As T
        Where CallbackDate >= Date() And T.CustomerId = CustomerCallback.CustomerId),
        (Select Max(CallbackDate) AS MaxCallbackDate
        From CustomerCallback As T
        Where CallbackDate < Date() And T.CustomerId = CustomerCallback.CustomerId)
        ) As NewCallbackDate
From 
    CustomerCallback
Group By 
    CustomerId

[Rabbit]

Be careful with correlated subqueries, they are very inefficient.

You can get the same results by joining the table to itself on the key and dates.

[lewish95]

You search through the list of dates, ignore any dates that are before your reference date, then remember the earliest of those dates.

Remembering the earliest date is like a regular loop for finding the minimum value.

Example:

private static Optional<LocalD ate> findNext(LocalD ate refDate, LocalDate[] dates) {
LocalDate next = null;
for (LocalDate date : dates)
if (! date.isBefore(r efDate) && (next == null || date.isBefore(n ext)))
next = date;
return Optional.ofNull able(next);
}
Test

LocalDate[] dates = { LocalDate.of(20 16, 1, 10),
LocalDate.of(20 16, 1, 20),
LocalDate.of(20 16, 1, 30) };
System.out.prin tln(findNext(Lo calDate.of(2016 , 1, 5), dates));
System.out.prin tln(findNext(Lo calDate.of(2016 , 1, 15), dates));
System.out.prin tln(findNext(Lo calDate.of(2016 , 1, 25), dates));
System.out.prin tln(findNext(Lo calDate.of(2016 , 2, 1), dates));
Output

Optional[2016-01-10]
Optional[2016-01-20]
Optional[2016-01-30]
Optional.empty

[Rabbit]

This is the Microsoft Access forum.