Building send and arrive times from multiple rows

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.

enter image description here

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

enter image description here

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

id CountryFrom CountryTo seqNo Send Arrive
1001 America China 1-2 2022-11-23 18:30:00 2022-11-24 05:00:00
1002 China Argentina 2-3 2022-11-24 08:18:00 2022-11-24 18:18:00
1003 Argentina Saudi Arabia 3-4 2022-11-25 18:30:00 2022-11-25 20:30:00

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.

Leave a Reply