1

Below table displays package transferred from origin to destination.

[![enter image description here][1]][1]

Explanation:

  1. Sent America 2022-11-23 18:30:00.000 Reached China 2022-11-24 05:00:00
  2. Sent China 2022-11-24 08:18:00.000 Reached Argentina 2022-11-24 18:18:00.000
  3. Sent Argentina 2022-11-25 18:30:00.000 and reached Saudi Arabia 2022-11-25 20:30:00.000
Honey Singh
  • 362
  • 8
  • Why do you need double number for seqNo? I'd use a regular integers; 1, 2, 3 etc. – jarlh Nov 24 '22 at 10:13
  • 1
    Hint: _self join_. – jarlh Nov 24 '22 at 10:14
  • @jarlh Primary focus is on the ContryFrom , CountryTo and Remarks. . SeqNo with intergers 1,2,3,4,5 is also okay. – Honey Singh Nov 24 '22 at 10:15
  • @jarlh I am trying using self join but since I'm not at all good in queries so facing issues. I'd appreciate your help. – Honey Singh Nov 24 '22 at 10:24
  • Self join is not anough as you need one self join for every node on a multi-path route. What you are looking for is a recursive query. See, for example, https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query – Stefan Winkler Nov 24 '22 at 10:58
  • @StefanWinkler Thank you for the hint but it seems my brain is spinning around looking the recursive query. – Honey Singh Nov 24 '22 at 11:01

1 Answers1

3

I hope the below answer is suitable for you.

less than <

select  f.id,f.Country CountryFrom, t.Country CountryTo
, convert(varchar(4),f.seqNo) + '-' + convert(varchar(4),t.seqNo) seqNo
, f.Send, t.Arrive,concat('Send ', f.Country ,' ', f.Send,' Reached ', t.Country,' ',t.Arrive) Remarks from IPhone f inner join IPhone t on f.seqNo < t.seqNo order by id;

Thanking you

logesh sankar
  • 233
  • 2
  • 8
  • 1
    Beautifully done. . Thank you so much Sirji. Apka bahut bahut dhanyawad. – Honey Singh Nov 24 '22 at 11:09
  • Note that this will work if you don't need the remarks part, because all information is available in the self join. If you need the "via" information from the Remarks column shown in the question, you need a recursive query. – Stefan Winkler Nov 24 '22 at 11:17
  • Yes, but the requirement is to populate the remarks only if there is VIA otherwise keep it blank. I think I need to use the recursive anyway. – Honey Singh Nov 24 '22 at 11:20
  • @StefanWinkler Can you please help me in the recursive section please. – Honey Singh Nov 24 '22 at 11:21