0

Hello.
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?

thanks,
jj

n.b.: (just in case you need it): primary key for the table is IDPERSONA+IDCATEGORIA+DATAPAGAMENTO

2
Contributors
1
Reply
2
Views
8 Years
Discussion Span
Last Post by hielo
0
SELECT idpersona, Max(idcategoria) as categoria, Max(datapagamento) as pagamento, Max(datascadenza) as scadenza 
FROM personacategoria  
WHERE NOW( ) 
BETWEEN datapagamento 
AND datascadenza 
GROUP BY idpersona
ORDER BY idpersona
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.