Esraa_92 Esraa_92 - 5 months ago 8
SQL Question

How can I get the next record date from a date and the last record date from a date?

I create table Appointments with this structure:

CREATE TABLE Appointments
(
[Id] bigint,
[Name] varchar(250),
[DateInit] date
);


INSERT INTO Appointments ([Id], [Name], [DateInit])
values
(1000, 'Lorena', '03/06/2016'),
(1000, 'Lorena', '01/06/2016'),
(1000, 'Lorena', '08/06/2016'),
(1000, 'Lorena', '10/06/2016'),
(1000, 'Lorena', '02/06/2016'),
(1000, 'Lorena', '20/06/2016'),
(7000, 'Susan', '04/06/2016'),
(7000, 'Susan', '08/06/2016'),
(7000, 'Susan', '09/06/2016'),
(7000, 'Susan', '01/06/2016');


This is the final result:

enter image description here

I need to get the result for the next day and the day before, for example if today is '03/06/2016' I need to get result for the last appointment inserted in the table from today and the next appointment inserted in the table from today, the result I need is something like this:

Name Last Visit Next Visit
----- ---------- -----------
Lorena 2016-06-02 2016-06-08
Susan 2016-06-01 2016-06-04


How can I get this result?
Thanks

Answer

Do a GROUP BY, use case expressions to pick max previous appointment, and min future appointment:

select name,
       max(case when DateInit < CONVERT(DATE,GETDATE()) then DateInit end) as LastVisit,
       min(case when DateInit > CONVERT(DATE,GETDATE()) then DateInit end) as NextVisit
from Appointments
group by name
Comments