0

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?

Improve this question
  • 4
    An EXCEPT query or a self-left-join-and-filter (anti-join) will work. If you post some sample data or dummy data to sqlfiddle.com I'll give a more complete answer, I can't be bothered making up a dummy schema to demo it. Any demo/dummy data should include rows accepted first time, rows rejected then accepted, and rows rejected and never followed up. – Craig Ringer Jun 4 '13 at 12:58
  • Exactly how do you know how the accepted and any nonaccetped records are relarted? Do they have the same id or some other field that ties them together? – HLGEM Jun 5 '13 at 13:44
4

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.