Where date difference between now and max date value is greater than X

Question

I want to return a result set (from postgres database) containing clients whose last payment date was more than X number of days ago.

This is what I have currently to get a list of all clients and their last payment date:

  select usermaster.userid, date_trunc('days', now()-max(paymentdate)) as last_payment
  from usermaster, paymentdetail 
  where usermaster.userid=paymentdetail.userid
  group by usermaster.userid;

I would like to limit with an extra where condition: where ... and last_payment > 100 days

score 0 · Answer 1 · answered Jun 05 '12 at 08:32

0

You could add having at the end:

having trunc('days', now()-max(paymentdate)) > 100

answered Jun 05 '12 at 08:32

Andomar

232,371
49
380
404

score 0 · Accepted Answer · edited Jun 05 '12 at 17:15

0

This should do the trick

EDIT -

select * from 
(SELECT usermaster.userid, date_trunc('days', now()-max(paymentdate)) as  
        last_payment
from usermaster, paymentdetail 
where usermaster.userid=paymentdetail.userid
group by usermaster.userid ) as temptab
where last_payment>100;

edited Jun 05 '12 at 17:15

Andomar

232,371
49
380
404

answered Jun 05 '12 at 08:33

ejb_guy

1,125
6
6

Thanks for the quick response. I hadn't encountered the HAVING option before. Unfortunately it doesn't work. Postgres gives ERROR: column "last_payment" does not exist – cemlo Jun 05 '12 at 10:30
Yes, this has worked for me. I was even able to modify it to be an update statement. Thanks! – cemlo Jun 07 '12 at 06:51

Where date difference between now and max date value is greater than X

2 Answers2