The following data is about a person sending an IpHone 14 pro max to his wife. The Iphone 14 pro max
has to travel from America to China, China to Argentina and finally from Argentina to Saudi Arabia since the girl is at Saudi Arabia
watching FIFA.

CREATE TABLE IPhone (Id NCHAR(10), Country NCHAR(10), seqNo NCHAR(10), Send date, Arrive date)

INSERT INTO IPhone VALUES 
('1001','America','1', '2022-11-23 18:30:00.000',null),
('1002','China','2', '2022-11-24 08:18:00.000','2022-11-24 05:00:00'),
('1003','Argentina','3', '2022-11-25 18:30:00.000','2022-11-24 18:18:00.000'),
('1004','Saudi Arabia','4',null,'2022-11-25 20:30:00.000')

Now the above results in the following.

Explanation:

1. Sent from America on 2022-11-23 18:30:00.000 and reached on China at
   2022-11-24 05:00:00
2. Sent from China on 2022-11-24 08:18:00.000 and
   reached on Argentina on 2022-11-24 18:18:00.000
3. Sent from Argentina
   on 2022-11-25 18:30:00.000 and reached on Saudi Arabia on 2022-11-25
   20:30:00.000

I am trying to achieve something like the following

>Solution :

You’re looking for a self join to the next row as shown below. However I’m not convinced the desired results you show are correct, because you are using the arrive value of the next destination which seems odd.

CREATE TABLE IPhone (Id int, Country NVARCHAR(12), seqNo int, Send datetime2(0), Arrive datetime2(0));

INSERT INTO IPhone VALUES 
('1001','America','1', '2022-11-23 18:30:00.000',null),
('1002','China','2', '2022-11-24 08:18:00.000','2022-11-24 05:00:00'),
('1003','Argentina','3', '2022-11-25 18:30:00.000','2022-11-24 18:18:00.000'),
('1004','Saudi Arabia','4',null,'2022-11-25 20:30:00.000');

SELECT f.id, f.Country CountryFrom, t.Country CountryTo
    , convert(varchar(4),f.seqNo) + '-' + convert(varchar(4),t.seqNo) seqNo
    , f.Send, t.Arrive
FROM IPhone f
INNER JOIN IPhone t ON t.seqNo = f.seqNo + 1
ORDER BY id;

Which returns:

DBFiddle

Note: Your sample data was broken:

• you were trying to store datetimes in a date
• you had incorrect dates
• you were storing numbers in strings
• and your string were too short for the data.

Getting this sort of thing right will make things much easier for the real work.