Return next customer visit date

Question

I have a table with unique customers in with their last visit date. I have another historical table that contains all of their visits plus any future dates booked in. I need to be able to return the next visit date after their last visit.

Can someone help as the problem I am having is that some customers have more than one future date booked in.

Answer 1

Create a query which gives you the history table rows whose visit dates are more recent than the customers' last visits.

SELECT
    c.CustomerID,
    h.VisitDate
FROM
    CustomerVisits AS c
    INNER JOIN History AS h
    ON c.CustomerID = h.CustomerID
WHERE h.VisitDate > c.LastVisit;

Once you have that working correctly, you can use it as a subquery in a GROUP BY query where you retrieve the minimum visit date for each customer.

SELECT
    sub.CustomerID,
    Min(sub.VisitDate) AS next_visit
FROM
    (
        SELECT
            c.CustomerID,
            h.VisitDate
        FROM
            CustomerVisits AS c
            INNER JOIN History AS h
            ON c.CustomerID = h.CustomerID
        WHERE h.VisitDate > c.LastVisit
    ) AS sub
GROUP BY sub.CustomerID;