I'm burning my head on a problem with a query.
Querying a payments table, I need to find the actual membership category (let's say: IDCATEGORIA). If I run this:
SELECT idpersona, idcategoria, datapagamento, datascadenza FROM personacategoria WHERE NOW( ) BETWEEN datapagamento AND datascadenza ORDER BY idpersona, datascadenza
I get this result:
idPersona idcategoria datapagamento datascadenza 2 2 2008-12-07 2010-03-31 4 2 2007-12-17 2009-03-31 4 1 2008-11-08 2010-03-31 5 2 2008-12-08 2010-03-31 6 2 2007-11-11 2009-03-31 7 2 2008-12-10 2010-03-31
The problem, as you can notice, is the second and third row: person 4 paid on the 2007-12-17 its membership, and then again one year later on the 2008-11-08. So, if I query now (2008-12-17), I obviously am in the overlapping period, so that I retrieve two records.
What I want to do is to retrieve, in cases like this, only the oldest DATASCADENZA record (obviously, for each single IDPERSONA).
I've tried lots of things today, but nothing seems to work... any hint?
n.b.: (just in case you need it): primary key for the table is IDPERSONA+IDCATEGORIA+DATAPAGAMENTO