CODEWITHSUNDEEP
sqldatabasepostgresql
norski_lab
norski_lab

Reputation: 41

Record has multiple statuses that gets rechecked. Only want records that meet a certain criteria

I have a table like below

Account number |  Last Name  |   Status       |   date
111                doe           acknowledged   04-11-2013
111                doe           acknowledged   05-01-2013
111                doe           paid           05-10-2013
123                smith         acknowledged   05-15-2013
123                smith         acknowledged   05-22-2013
145                walter        paid           05-23-2013

There are names and account numbers that holder the same information but just have different statuses and dates.

I am trying to get the most recent date and compare it to the current date. So for today I would compare doe with 5-10, smith with 5-22 and walter with 5-23...

Now an account can get rechecked multiple times and it will only stop getting rechecked once it has been paid. So smith would be the only one to get recheck at a later date.

I am wanting to find all of the records that have an acknowledged status. I do not want any of these to be paid.

So far in my code I able to get the max date for a record, but it brings back the acknowledged and the paid record since they are both distinct. I only want records that have not been paid and that are still acknowledged.

Upvotes: 0

Views: 74

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270703

You seem to want the most recent acknowledged status for accounts that are not paid. Here is a query for that:

select AccountNumber, Name,
       max(date)
from t
group by AccountNumber, Name
having sum(case when status = 'Paid' then 1 else 0 end) = 0

You only have the two different statuses in your question. If you had more and you wanted just the acknowledged max date, then you would do:

select AccountNumber, Name,
       max(case when status = 'Acknowledged' then date end)
from t
group by AccountNumber, Name
having sum(case when status = 'Paid' then 1 else 0 end) = 0

To get the most recent status, you can use row_number() to enumerate the rows:

select AccountNumber, Name,
       max(case when status = 'Acknowledged' then date end),
       max(case when seqnum = 1 then status end) as MostRecentStatus
from (select t.*,
             row_number() over (partition by AccountNumber, Name order by date desc) as seqnum
      from t
     ) t
group by AccountNumber, Name
having sum(case when status = 'Paid' then 1 else 0 end) = 0

Upvotes: 1

Related Questions