Get Last and Next Appointments

Question

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.

As per the question guide please show what you have tried and where you got stuck. — Dale K, Aug 07 '21 at 12:08
I tried to do it with various ways, e.g. Variable Tables, Sub Querries, but am getting stuck, hence why I asked here. This is my first question here, so not sure exactly how you show proof that I tried this before. — Vladimir Lenin, Aug 07 '21 at 12:12

forpas · Answer 1 · 2021-08-07T13:41:45.337

You need conditional aggregation:

SELECT CustomerId,
       COALESCE(
         MAX(CASE 
               WHEN CAST(Date AS DATETIME) + CAST(Time AS DATETIME) < GETDATE() 
                 THEN FORMAT(CAST(Date AS DATETIME) + CAST(Time AS DATETIME), 'dd MMM yyyy - HH:mm')
             END
         ), 'N/A'    
       ) LastAppointment,
       COALESCE(
         MIN(CASE 
               WHEN CAST(Date AS DATETIME) + CAST(Time AS DATETIME) > GETDATE() 
                 THEN FORMAT(CAST(Date AS DATETIME) + CAST(Time AS DATETIME), 'dd MMM yyyy - HH:mm')
             END
         ), 'N/A'    
       ) NextAppointment
FROM tablename
GROUP BY CustomerId
HAVING COALESCE(DATEDIFF(
         d, 
         MAX(CASE 
               WHEN CAST(Date AS DATETIME) + CAST(Time AS DATETIME) < GETDATE() 
                 THEN CAST(Date AS DATETIME) + CAST(Time AS DATETIME)
             END
         ),
         GETDATE()
       ), 0) < 30

See the demo.
Results:

CustomerId	LastAppointment	NextAppointment
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

#4 is not in the OP's desired results. – Stu Aug 07 '21 at 13:28 — Stu, Aug 07 '21 at 13:28
@Stu correct, I added a HAVING clause that solves this. – forpas Aug 07 '21 at 13:42 — forpas, Aug 07 '21 at 13:42

Stu · Answer 2 · 2021-08-07T13:40:10.377

You simply need to work with datetime values and then use conditional aggregation to select the required date for each customer. Using a CTE first to simplify converting the dates as much as possible, this looks like:

with ap as (
    select CustomerId, Convert(datetime,Left(Concat([date], ' ', [time]),15)) app
    from t
), groups as (
    select CustomerId, 
    Max(case when app <= GetDate() then app end) LastAppointment,
    Min(case when app > GetDate() then app end) NextAppointment
    from ap
    group by customerId
)
select CustomerID, 
    IsNull(Format(LastAppointment, 'dd MMM yyyy - hh:mm'), 'N/A') LastAppointment, 
    IsNull(Format(NextAppointment, 'dd MMM yyyy - hh:mm'), 'N/A') NextAppointment
from groups
where DateAdd(day,-30,GetDate()) < isnull(lastappointment,GetDate())

see DB<>Fiddle

Also note this query only touches the table once and performs a single logical read.

Stefano Sansone · Accepted Answer · 2021-08-07T14:13:08.237

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.

Thanks, much appreciated. Very close. Is there some way to alter these in the WHERE clause to also include the time - [Date] <= @currentDate. Because at the moment if the next appointment is in the next hour, it is shown in the Last Appointment column, rather than the Next Appointment ? — Vladimir Lenin, Aug 07 '21 at 12:53
note that if performance is a concern, this query performs 94 logical reads where only 1 is actually necessary. — Stu, Aug 07 '21 at 13:38
I agree, the solution from @Stu is better, I'll update my answer with a reference — Stefano Sansone, Aug 07 '21 at 14:08

score 0 · Answer 4 · answered Aug 07 '21 at 15:04

Base table is used only single time because of optimization purpose. Use LAG() function and others necessary condition for picking actual set of data.

-- SQL SERVER

SELECT p.CustomerId
     , CASE WHEN p.chk_condition = 1
               THEN CONVERT(varchar(13), p.prev_Date, 113) + ' - ' + LEFT(p.prev_time, 5)
            WHEN p.chk_condition = 2
               THEN CONVERT(varchar(13), p.Date, 113) + ' - ' + LEFT(p.time, 5)
            ELSE 'N/A'
       END "LastAppointment"
     , CASE WHEN p.chk_condition != 2
               THEN CONVERT(varchar(13), p.Date, 113) + ' - ' + LEFT(p.time, 5)
            ELSE 'N/A'
       END "NextAppointment"
FROM ( SELECT t.*
            , CASE WHEN  t.prev_Date < GETDATE() AND t.Date >= GETDATE()
                      THEN 1
                   WHEN t.prev_Date < GETDATE() AND t.Date <= GETDATE()
                      THEN 2
                   ELSE 0
              END chk_condition
            , ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY t.Date DESC, t.prev_Date DESC) row_num
       FROM (SELECT CustomerId, Date, Time
                  , LAG(Date) OVER (PARTITION BY CustomerId ORDER BY "Date", "Time") "prev_Date"
                  , LAG(Time) OVER (PARTITION BY CustomerId ORDER BY "Date", "Time") "prev_Time"
             FROM appointment) t
       WHERE  CASE WHEN t.prev_Date < GETDATE() AND t.Date >= GETDATE() 
                      THEN 1
                   WHEN t.prev_Date IS NULL 
                      THEN CASE WHEN DATEDIFF(day, t.Date, GETDATE()) >= 30
                                   THEN 0
                                ELSE 1
                           END
                   WHEN t.prev_Date < GETDATE() AND t.Date <= GETDATE()
                      THEN 1
               END = 1 ) p
WHERE p.row_num = 1
ORDER BY p.CustomerId;

Please check this url https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=3813d09cf25ed14d249970654995b085

Hi @VladimirLenin I know you've picked the right one which you desire. I've tried to writing this query as optimized as possible so that it can handle large volume of data. Please check my query if you want. — Rahul Biswas, Aug 07 '21 at 15:16

Get Last and Next Appointments

4 Answers4