Comparing Row Values
The source call leg does not change in the whole process.


You need to remember that Name and Number also stay the same. So, your IN predicate should include those column in the matching:

  (tb.name, tb.number, tb.Src_Call_Leg) IN (
    SELECT
      tb1.name, tb1.number, tb1.Src_Call_Leg
    FROM
      yourTable tb1
    WHERE
      tb1.Dest_Call_Leg LIKE '1000%'
  )


Not all SQL products support tuple comparison, though, and you have not specified what is yours, so the above syntax might not work for you.

Still, there is an alternative that will most likely work: an EXISTS predicate. It will go like this:

  EXISTS (
    SELECT
      *
    FROM
      yourTable tb1
    WHERE
      tb1.Dest_Call_Leg LIKE '1000%'
      AND tb1.name = tb.name
      AND tb1.number = tb.number
      AND tb1.Src_Call_Leg = tb.Src_Call_Leg
  )


And since you have also specified that you want only the final Dest_Call_Leg, you probably need this additional condition in the main WHERE:

  AND tb.Dest_Call_Leg NOT LIKE '1000%'


So, the entire query would look like this:

SELECT
  tb.name,
  tb.number,
  tb.Src_Call,
  tb.Dest_Call_leg
FROM
  yourTable tb
WHERE
  EXISTS (
    SELECT
      *
    FROM
      yourTable tb1
    WHERE
      tb1.Dest_Call_Leg LIKE '1000%'
      AND tb1.name = tb.name
      AND tb1.number = tb.number
      AND tb1.Src_Call_Leg = tb.Src_Call_Leg
  )
  AND tb.Dest_Call_Leg NOT LIKE '1000%'
;


Alternatively, if your SQL product supports analytic functions like LAST_VALUE(), you could implement the request like this:

SELECT
  Name,
  Number,
  Src_Call_Leg,
  Final_Dest_Call_Leg AS Dest_Call_Leg
FROM
  (
    SELECT
      Name,
      Number,
      Src_Call_Leg,
      Dest_Call_Leg,
      LAST_VALUE(Dest_Call_Leg) OVER (
        PARTITION BY Name, Number, Src_Call_Leg
        ORDER BY ID ASC
        ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
      ) AS Final_Dest_Call_Leg
    FROM
      yourTable tb1
  ) derived
GROUP BY
  Name,
  Number,
  Src_Call_Leg,
  Final_Dest_Call_Leg
HAVING
  COUNT(CASE WHEN Dest_Call_Leg LIKE '1000%' THEN 1 END) &gt; 0
;


The derived table calculates the final Dest_Call_Leg for every call. Columns Name, Number, Src_Call_Leg and Final_Dest_Call_Leg will stay the same across every group of rows that belong to the same call – so the main SELECT groups the derived table by those columns and uses HAVING to filter out the groups that do not have an intermediate Dest_Call_Leg that matches the 1000% pattern.