Postgresql – Need an idea for a complex SQL question

postgresql

I have had my cup of coffee, but cannot come up with a good solution for the following problem – so I am here to pick your brain. 😉

Here is what I am trying to accomplish (for PostgreSQL):

  • Got a table with logged communications related to products.
  • The table contains an ID (PK, serial), date/time when inserted, a product ID, an error code field (and some other fields not relevant for now)
  • Rows get validated upon reception, and all rows are stored.
  • Rows are either accepted or rejected (error code field).
  • When accepted, fine, the rows get processed (as a 'step 2' – not relevant for now).
  • However, when rejected the sender gets an error message and is requested to correct the data and resend.
  • So, when the corrected data is received again and is validated and accepted, we should have a product row that got rejected and a product row that got accepted.
  • We have learned that not all senders check these error message and resend their (corrected) data.

So, what I am looking for are these rows for product that were rejected but never sent again correctly – it may happen that rows get send multiple times and rejected multiple times by our validation process.

I have been at it for a couple of hours, but so far without much luck.
What SQL trick could I pull out of the hat to find these rows?

Best Answer

It sounds like what you're looking for is the list of communications for which there is no accepted message. You're only interested in checking for communications where there is at least one rejection, so the logic might be something like:

select distinct communications
where  the status is rejection
and there does not exist an acceptance for that communication.

Alternatively, you could check the status on the record with the highest received timestamp for each communication to ensure that it is an acceptance.

Related Question