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.
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.