...

View Full Version : how to get next date?



mwm
03-20-2007, 07:01 PM
I have a contact form that inserts the date the last time a sales person has made contact with the buyer. I want to display this in a report, so when they run the report it show the last date contact was made. something like select next closes date to current date. any ideas how this could be done?

Thanks

Fumigator
03-20-2007, 08:52 PM
SELECT MAX(contactDate) FROM contactTable where buyer = $buyer will select the highest (most current) date. Is that what you're after?

mwm
03-20-2007, 10:09 PM
Yes that's what I was going for, but still can't make it work. Here is the query



$mysql = ("select cust.id,cust.fname,cust.lname,cust.address,cust.city,cust.state,cust.zip,cust.hphone,cust.community, date_format(cust.regdate, '%m-%d-%Y'), sum(followup.tpcount) as t_count, MAX(followup.tpdate) from cust, followup where cust.regdate < curdate() - interval 0 day and community = '$community' and cust.id = followup.id and status = 'Prospect' group by lname having t_count < 1");

Fumigator
03-20-2007, 10:54 PM
SUM() and MAX() are column functions so they can't be mixed with non-column functions... basically when you do a "GROUP BY" you you can only select columns that are in that GROUP BY clause.

A way around this is to do a subquery, but your version of MySQL must be 4.1 or greater.



SELECT *
FROM table1 as a
WHERE date1 = (select MAX(date1) from table1 as b WHERE a.id = b.id)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum