sqlsql-servert-sqlsqldatetime

Get Last and Next Appointments


I have the following table in SQL Server and would like to get the last and next appointments for each customer.

Note: If the first appointment is in the future, last appointment should be N/A. Similarly if the last appointment is in the past, next appointment will be N/A. If the last appointment is older than 30 days it should not be shown (if there is no future appointment - considered an inactive customer).

CustomerId (int) | Date (date) | Time (time)
1                | 20210801    | 11:00
1                | 20210802    | 13:00
1                | 20210805    | 10:00
1                | 20210811    | 16:00
1                | 20210821    | 17:00
2                | 20210801    | 11:00
2                | 20210802    | 11:00
2                | 20210803    | 11:00
2                | 20210804    | 11:00
3                | 20210831    | 11:00
4                | 20210526    | 10:00

In this case the result should be (Assuming the date is today 7 August 2021):

CustomerId (int) | LastAppointment (varchar) | NextAppointment (varchar)
1                | 05 Aug 2021 - 10:00       | 11 Aug 2021 - 16:00
2                | 04 Aug 2021 - 11:00       | N/A
3                | N/A                       | 31 Aug 2021 - 11:00

Can anyone help me please? An example would be appreciated.


Solution

  • NOTE : This solution works but it is very bad in terms of performance, check this answer for a better approach


    Something like this

    SELECT DISTINCT customerid,
                    Isnull(CONVERT(VARCHAR,
                                     (SELECT TOP 1 Concat(date, ' ', TIME)
                                      FROM appointments B
                                      WHERE b.customerid = a.customerid
                                        AND ([date] < CONVERT(DATE, Getdate())
                                             OR ([date] = CONVERT(DATE, Getdate())
                                                 AND [time] <= CONVERT(TIME, Getdate())))
                                        ORDER  BY [date] DESC)), 'N/A') AS lastappointment,
                    Isnull(CONVERT(VARCHAR,
                                     (SELECT TOP 1 Concat(date, ' ', TIME)
                                      FROM appointments B
                                      WHERE b.customerid = a.customerid
                                        AND ([date] > CONVERT(DATE, Getdate())
                                             OR ([date] = CONVERT(DATE, Getdate())
                                                 AND [time] > CONVERT (TIME, Getdate())))
                                        ORDER  BY [date])), 'N/A') AS nextappointment
    FROM appointments A
    WHERE Datediff(DAY,
                     (SELECT TOP 1 date
                      FROM appointments B
                      WHERE b.customerid = a.customerid
                        AND [date] <= CONVERT(DATE, Getdate())
                        ORDER  BY [date] DESC), CONVERT(DATE, Getdate())) <= 30
      OR (((
              (SELECT TOP 1 date
               FROM appointments B
               WHERE b.customerid = a.customerid
                 AND [date] > CONVERT(DATE, Getdate())
                 ORDER  BY [date]) > CONVERT(DATE, Getdate())))
          OR ((
                 (SELECT TOP 1 date
                  FROM appointments B
                  WHERE b.customerid = a.customerid
                    AND [date] > CONVERT(DATE, Getdate())
                    ORDER  BY [date]) = CONVERT(DATE, Getdate()))
              AND (
                     (SELECT TOP 1 [time]
                      FROM appointments B
                      WHERE b.customerid = a.customerid
                        AND [date] > CONVERT(DATE, Getdate())
                        ORDER  BY [date]) > CONVERT(TIME, Getdate()))))
    

    I called your table appointments and the condition is to select customer with last appointment in the past 30 days OR with a future appointment. I tested with column types Date for Date and Time(7) for time.

    enter image description here