Comparing Row Values

Question

I'm a SQL newbie and would like some help please.

This is my table:

ID  Name  Number  Src_Call_Leg  Dest_Call_Leg
--  ----  ------  ------------  -------------
1   John  5555    100           1000
2   John  5555    100           102

The table stores call records. When a call is made to 5555, the system first took source call leg 100 and destination call leg 1000 and a final destination call leg of 102 before it was answered by John. The source call leg does not change in the whole process.

What I'd like is to show only the data which has Name, Number, Source Call Leg and the final destination call leg for records where the interim call leg starts with 1000.

I tried doing this but it didn't work. This is my attempt:

select
  tb.name,
  tb.number,
  tb.Src_Call,
  tb.Dest_Call_leg
from
  table tb
where
  tb.Src_Call_Leg IN (
    select
      tb1.Src_Call_Leg
    from
      table tb1
    where
      tb1.Dest_Call_Leg like '1000%'
  );

I know I'm doing something really silly, would appreciate it if someone could point that out.

looks like SQL Server from the like '1000%' – Hannah Vernon Sep 16 '16 at 14:28 — Hannah Vernon, Sep 16 '16 at 14:28

Andriy M · Answer 1 · 2016-09-16T05:50:20.520

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) > 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.

Comparing Row Values

1 Answers1