how to do a filter from a table where 2 different columns has 2 different records which has same set of key combinations in bigquery?

Question

I am working with millions of data where i wanted to filter 2 different columns having 2 different records for the same set of key combination,

The table looks like :

SK	NO	NAME	DOB	TS	EFF_DT	SOURCE
123	1	Thomas	1/12/1994	2024-29-03T00:00:00	24-03	ABC
123	1	Thomas	1/12/1994	2024-29-03T12:00:00	24-05	ABC
456	1	Thomas	1/12/1994	2024-29-04T00:00:00	14-03	CBA
456	1	Thomas	1/12/1994	2024-29-04T12:00:00	14-03	CBA

I wanted to filter the records based on key columns combinations that is SK and NO columns are same for different TS and eff_dt and not wanted pick records where TS is alone different.

can someone please explain on how to achieve this?

thanks in advance!

The expected output:

SK	NO	NAME	DOB	TS	EFF_DT	SOURCE
123	1	Thomas	1/12/1994	2024-29-03T00:00:00	24-03	ABC
123	1	Thomas	1/12/1994	2024-29-03T12:00:00	24-05	ABC

Answer 1

First, we identify combinations of SK and NO that have two unique values in two separate columns using the GROUP BY and HAVING clauses :

SELECT SK, `NO`
FROM mytable
GROUP BY SK, `NO`
HAVING COUNT(DISTINCT TS) = 2 AND  COUNT(DISTINCT EFF_DT) = 2

Then, to obtain the desired results, we merge this with the original table:

SELECT t.*
FROM mytable t
INNER JOIN (
  SELECT SK, `NO`
  FROM mytable
  GROUP BY SK, `NO`
  HAVING COUNT(DISTINCT TS) = 2 AND  COUNT(DISTINCT EFF_DT) = 2
) AS s ON s.SK = t.SK AND s.NO = t.NO