mdarr mdarr - 7 months ago 15
SQL Question

Conditional Row Deleting in SQL

I have a table that contains 4 columns. I need to remove some of the rows based on the Code and ID columns. A code of 1 initiates the process I'm trying to track and a code of 2 terminates it. I would like to remove all rows for a specific ID when a code of 2 comes after a code of 1 and there is not an additional code 1. For example, my current data set looks like this:

Code Deposit Date ID
1 $100 3/2/2016 5
2 $0 3/1/2016 5
1 $120 2/8/2016 5
1 $120 3/22/2016 4
2 $70 2/8/2016 3
1 $120 1/3/2016 3
2 $0 6/15/2015 2
1 $120 3/22/2016 2
1 $50 8/15/2015 1
2 $200 8/1/2015 1


After I run my script I would like it to look like this:

Code Deposit Date ID
1 $100 3/2/2016 5
2 $0 3/1/2016 5
1 $120 2/8/2016 5
1 $120 3/22/2016 4
1 $50 8/15/2015 1
2 $200 8/1/2015 1


In all I have about 150,000 ID's in my actual table but this is the general idea.

Answer

You can get the ids using logic like this:

select t.id
from t
group by t.id
having max(case when code = 2 then date end) > min(case when code = 1 then date end) and -- code 2 after code 1
       max(case when code = 2 then date end) > max(case when code = 1 then date end) -- no code 1 after code2

It is then easy enough to incorporate this into a query to get the rest of the details:

select t.*
from t
where t.id not in (select t.id
                   from t
                   group by t.id
                   having max(case when code = 2 then date end) > min(case when code = 1 then date end) and -- code 2 after code 1
                          max(case when code = 2 then date end) > max(case when code = 1 then date end)
                  );
Comments