Checking accounts satisfying specific requirements

Question

I need to identify accounts that were rated on a specific date (February 2010) and no more in another one (December 2010).

The table looks like

account date               Rated
451234  January 2008       rated
451234  February 2008      rated
451234  March 2008         not rated
451234  December 2010      not rated
214211  January 2008       rated
214211  February 2009      rated
241243  March 2011         rated
241243  December 2010      not rated
241243  March 2009         not rated
241243  April 2009         not rated
241243  March 2012         not rated
241243  December 2011      not rated

The table shown above was created by an inner join:

select * from 
tab1 as t1
inner join tab2 as t2
on t1.account=t2.account
and t1.date=t2.date

Do you know how to check accounts that were rated on February 2010 but no longer in December 2010?

Result that I expect:

account date               Rated
451234  February 2008      rated
451234  December 2010      not rated
241243  December 2010      not rated

Answer 1

I need to identify accounts that were rated on a specific date (February 2010) and no more in another one (December 2010).

If I understand correctly, then one method is aggregation with having:

select account
from tab1
group by account
having sum(case when date = 'February 2010' and rated = 'rated' then 1 else 0 end) > 0 and
       sum(case when date = 'Decemner 2010' and rated = 'not rated' then 1 else 0 end) > 0 ;

And alternative method would be not exists:

select t1.*
from tab1 t1
where t1.date = 'February 2010' and
      t1.rated = 'rated' and
      not exists (select 1
                  from tab1 tt1
                  where tt1.account = t1.account and
                        tt1.date = 'December 2010' and
                        tt1.rated = 'not rated'
                 );