SQL Query to transfer the data to another table based on filter

Question

Currently I have the data in a table that look like below:

#Tbl_A

Id  Customer_Id     Indicator
1   912538132       1  
2   912538132       2
3   912538132       3
4   912538132       1
5   912538132       1
6   912538132       2
7   912538132       1
8   912538132       2
9   912538132       3
10  912538132       4

Here I need to transfer the above #Tbl_A data to #Tbl_B table by applying a filters like: the indicator should be greater than or equal to 3.

EXAMPLE-DATA after loading the data into the #Tbl_B

#Tbl_B

Id  Customer_Id     Indicator
1   912538132       1  
2   912538132       2
3   912538132       3

7   912538132       1
8   912538132       2
9   912538132       3
10  912538132       4

NOTE: The rest of the data should not insert into the #Tbl_B..since the sequence of indicators are not greater than or equal to 3.

I have tried with BETWEEN and IN operators. But I am getting the unexpected results in the #Tbl_B.

score 2 · Answer 1 · answered Sep 11 '20 at 21:24

This is a gaps-and-islands problem, where you need to build groups of adjacent records where the indicator is in sequence. Then, you want to filter out groups that do not contain indicator 3.

If id always increment by 1 without gaps, then we can use the difference between its value and the increment to build the groups:

insert into #tableb (id, customer_id, indicator)
select id, customer_id, indicator
from (
    select 
        a.*, 
        max(case when indicator = 3 then 1 else 0 end)
            over(partition by customer_id, id - indicator) has_3
    from #tablea a
) a
where has_3 = 1

Else, you can generate another id with row_number():

insert into #tableb (id, customer_id, indicator)
select id, customer_id, indicator
from (
    select 
        a.*, 
        max(case when indicator = 3 then 1 else 0 end)
            over(partition by customer_id, new_id - indicator) has_3
    from (
        select 
            a.*, 
            row_number() over(partition by customer_id order by id) new_id
        from #tablea a
    ) a
) a
where has_3 = 1

score 0 · Answer 2 · answered Sep 11 '20 at 21:14

This seems to work for me...

    DROP TABLE IF EXISTS #tableA, #tableB;
CREATE TABLE #tableA  (id INT, customer_id INT, indicator INT) ;
CREATE TABLE #tableB  (id INT, customer_id INT, indicator INT) ;
INSERT INTO #tableA
(
    id
  , customer_id
  , indicator
)
VALUES
(1  ,912538132, 1)
,(2 ,912538132, 2)
,(3 ,912538132, 3)
,(4 ,912538132, 1)
,(5 ,912538132, 1)
,(6 ,912538132, 2)
,(7 ,912538132, 1)
,(8 ,912538132, 2)
,(9 ,912538132, 3)
,(10    ,912538132, 4);



INSERT INTO #tableB
(
    id
  , customer_id
  , indicator
)
SELECT id, customer_id, indicator FROM #tableA WHERE indicator >=3


SELECT * FROM #tableB;

I get IDs 3,9, and 10. Tha's what you're looking for, right?

SQL Query to transfer the data to another table based on filter

2 Answers2