0

I try to compare a date with dates which are in different rows. I hope the picture makes it clear.

For example:

The Product with ID "2" in "June" should have a fee of 100$ because the fee was valid from April to July.

enter image description here

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Stam
  • 35
  • 5
  • 1
    Welcome to Stack Overflow! Please note that plain *text* is very much preferred for data. Not images. Always declare your version of Postgres. And try to make your task clear. There is room for interpretation in your question. – Erwin Brandstetter Apr 17 '20 at 22:47

1 Answers1

0

For a single given product_id:

SELECT * -- which columns do you need?
FROM   product    p
JOIN   productfee f ON f.valid_from <= p.date
WHERE  p.product_id = 2
ORDER  BY f.valid_from DESC
LIMIT  1;

If tables are of non-trivial size, you'd better have at least these indexes:

CREATE INDEX ON product (product_id);
CREATE INDEX ON productfee (valid_from DESC);

(Or more specialize, depending on the complete picture.)

If valid_from can be NULL, consider NULLS LAST. See:

Obviously, you'll use proper date or timestamp types, not just month names.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228