SQL Query direct or inner join

Question

i want to find a number of clients from certain dates before and after there was a status changes in their credit history. eg:

• date = 2010/07/25
• date2 = 2010/08/30

i want everyone from the table who had a status "pending" before "date" and from the same lists of people, i want to identify lists of clients whose status changed from "pending" to "approved" after "date2". eg

• before "date", i had 20k clients with "pending" status
• after "date2", i still have 20k clients but some went from pending to approved and i want to identify these clients

below is my table description:

• i have a table that store just unique information
• the second is a transaction table of the first table, that records every single activity with a timestamp. its really detailed minute by minute table records.

i tried doing a direct join with these two tables, but still wasn't sure if i was getting everyone. the reason is, the second transaction table stores every status "states" (pending, approved) and time.

what is common in both tables are:

• status
• client_id

Answer 1

This will return a list of clients who have at least one transaction that was:

• before 2010-07-25, with a status of "pending"
• after 2010-08-30, with a status of "approved"

Query

SELECT c.*
  FROM CLIENTS c
 WHERE EXISTS(SELECT NULL
                FROM TRANSACTIONS t
               WHERE t.client_id = c.client_id
                 AND t.status = 'pending'
                 AND t.transaction_date < 2010-07-25)
   AND EXISTS(SELECT NULL
                FROM TRANSACTIONS t
               WHERE t.client_id = c.client_id
                 AND t.status = 'approved'
                 AND t.transaction_date > 2010-08-30)

If you want the count of those, use:

SELECT COUNT(*)
  FROM CLIENTS c
 WHERE EXISTS(SELECT NULL
                FROM TRANSACTIONS t
               WHERE t.client_id = c.client_id
                 AND t.status = 'pending'
                 AND t.transaction_date < 2010-07-25)
   AND EXISTS(SELECT NULL
                FROM TRANSACTIONS t
               WHERE t.client_id = c.client_id
                 AND t.status = 'approved'
                 AND t.transaction_date > 2010-08-30)

The reason I don't use a JOIN is because that would duplicate rows in the resultset, for every transaction record that matched the criteria--you'd need to use either DISTINCT or GROUP BY to get rid of the duplicates. EXISTs will return true if there are one or more instances of matching criteria, and doesn't make duplicates in the resultset.

Answer 2

Assuming you won't go back from Approved to Pending, You can do it using subquery:

select * from table2 t2 inner join table1 t1 on t2.client_id = t1.client_id where t2.client_id in (select distinct t3.client_id from table2 t3 where t3.status='Pending' and t3.date<'2010/07/25') and t2.status = 'Approved' and t2.date > '2010/07/30'