MAX(date) mysql not working

try

$sql = "SELECT 
customer_info.id                                    AS customer_info_id,
customer_info.customer_number                       AS customer_number,
sales_info.id                                       AS sales_info_id,
sales_info.custname                                 AS custname,
sales_info.xtndprce                                 AS xtndprce,
max(sales_info.docdate)                                  AS docate
".

 "FROM customer_info ".
 "LEFT JOIN sales_info AS sales_info ON sales_info.custnmbr = customer_info.customer_number 
  

  
  WHERE customer_info.customersalespersonid  ='$emp_id' AND sales_info.docdate > '$four_months' GROUP by customer_info.id,
         customer_info.customer_number,
         sales_info.id,
         sales_info.custname,
         sales_info.xtndprce";

Select allOpen in new window

In addition to choosing the max(sales_info.docdate) i need it to group by custnmbr as well..It is still displaying older items than they '$four_months' date specified..

max(sales_info.docdate) in this query does not make sense...

each customer will have multiple sales and do you want maxdate for that customer???

CustID SaleID saleDate maxSaleDate
1 1001 10/10/2009 23/08/2010
1 1031 27/12/2009 23/08/2010
1 1236 05/02/2010 23/08/2010
1 1731 23/08/2010 23/08/2010

do you want something like above????

try like this then

SELECT 
customer_info.id                                    AS customer_info_id,
customer_info.customer_number                       AS customer_number,
sales_info.id                                       AS sales_info_id,
sales_info.custname                                 AS custname,
sales_info.xtndprce                                 AS xtndprce,
sales_info.docdate                                  AS docate
".

 "FROM customer_info ".
 "LEFT JOIN sales_info AS sales_info ON sales_info.custnmbr = customer_info.customer_number 
  

  
  WHERE customer_info.customersalespersonid  ='$emp_id' AND sales_info.docdate > '$four_months' 
  and sales_info.docdate = (select max(docdate) from sales_info t1 where t1.custnmbr = sales_info.custnmbr)";

Select allOpen in new window

it is taking a really long time to load, so i am not sure what the results are that it is returning...

Basically I have two tables..

1. customer_info
2. sales_info

What I am trying to do is choose all off the rows that = to a specific sales person id (customer_info.customersalespersonid  ='$emp_id') as listed above...Then I need to compare it to my sales_info table...

The sales_info table have multiple entries in it for most stores. When I join the two tables, if mysql finds a match between the customer_info table and the sales_info table then I need to it find all the results with the sames custnmbr and group them together by docdate, with the max docdate having the docdate value..I then only want to echo the values that have nothing matched and ones that are >$four_months ..

Hope that makes more sense...

can you please post some sample data for two tables and result that you want to get from these sample data...
so everybody will be on the same track...

Below is an image of the sales table for custmnbr LOWE0202. This is a smalll sample. In the customer table LOWE0202 is one of the rows that equals the sales person id.

I need to look at the sales_info table and find all LOWE0202 entries. If there are any 'docdate' that are newer than fourth months than I do not want to display it. I only want to display the custmnbr that has sales greater than four months...Thanks
mysqlimg.jpg

Ok so for a little more clarification, below are two php functions..The first pulls out all of the sales data using the mas date, so I get a list of results.
Now the second scrupt pulls out every customer that the sales person has.

I only want to display the results in the second query that do not match the results of the 1st query...

thanks

<?php
$con = mysql_connect('localhost', 'brad', 'sbp06');
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("cms", $con);

$quarter =  date("Y-m-d ");
$four_months = date('Y-m-d', strtotime($quarter . ' -17 week'));
$sql = "SELECT 
sales_info.id                                             AS sales_info_id,
MAX(sales_info.docdate)                                   AS docdate,
sales_info.custnmbr                                       AS custnmbr
".

 "FROM sales_info ".
 " WHERE SLPRSNID='KLINTON' AND docdate > '$four_months' GROUP BY sales_info.custnmbr ORDER by docdate"; 

$result = mysql_query($sql);
while($row = mysql_fetch_array($result)){

$custnmbr = $row['custnmbr'];
$docdate = $row['docdate'];
echo "$custnmbr $docdate<br>";
}
?>


<?php
$sql = "SELECT 
customer_info.id                                                  AS customer_info_id,
customer_info.customer_number                                     AS customer_number,
customer_info.customersalespersonid                               AS customersalespersonid

".

 "FROM customer_info ".
 " WHERE customersalespersonid='KLINTON'"; 
 
 
echo "<br><br><br><br>";
$result = mysql_query($sql);
while($row = mysql_fetch_array($result)){

$customer_number = $row['customer_number'];
echo "$customer_number<br>";
}
?>

Select allOpen in new window

>>I only want to display the results in the second query that do not match the results of the 1st query...<<

I'm still not very clear with what you want. But it seems that changing the > to <= should be what you're looking for
WHERE SLPRSNID='KLINTON' AND docdate <= '$four_months' GROUP BY sales_info.custnmbr ORDER by docdate";  
If that's not the case, please post the expected result from the sample data you've provided above and explain the rationale behind your selection.
 

The first query I am running shows every store that should NOT be displayed in the second query...

I am not really sure how to explain it any other way...
I have a long list in customer_info

I have a small list in sales_info

I only want to display the results of the custoemr_info query that are not displayed in the sales_info query.

Basically I want to use the sales_info query as a filter for the customer_info query.

maybe this is the query you're looking for:

select a.* from (
	SELECT 
		customer_info.id                                                  AS customer_info_id,
		customer_info.customer_number                                     AS customer_number,
		customer_info.customersalespersonid                               AS customersalespersonid
	FROM customer_info
	WHERE customersalespersonid='KLINTON'
) as a
left join (
	SELECT 
		sales_info.id                                             AS sales_info_id,
		MAX(sales_info.docdate)                                   AS docdate,
		sales_info.custnmbr                                       AS custnmbr
	FROM sales_info
	WHERE SLPRSNID='KLINTON' AND docdate > '$four_months' GROUP BY sales_info.custnmbr 	
) b on a.sales_info_id = b.sales_info_id and a.custnmbr = b.custnmbr
where b.sales_info_id is null

Select allOpen in new window

When I run the query in my sql I get an error

#1054 - Unknown column 'a.sales_info_id' in 'on clause'

messed up with the column names there.
I'm not sure how if sales_info_id should be joined to customer_info_id so I've just joined them with the customer_number  like
) b on a.customer_number = b.custnmbr
if customer_info_id should match to sales_info_id as well, then do
) b on a.customer_number = b.custnmbr and a.customer_info_id = b.sales_info_id
 

select a.* from (
	SELECT 
		customer_info.id                                                  AS customer_info_id,
		customer_info.customer_number                                     AS customer_number,
		customer_info.customersalespersonid                               AS customersalespersonid
	FROM customer_info
	WHERE customersalespersonid='KLINTON'
) as a
left join (
	SELECT 
		sales_info.id                                             AS sales_info_id,
		MAX(sales_info.docdate)                                   AS docdate,
		sales_info.custnmbr                                       AS custnmbr
	FROM sales_info
	WHERE SLPRSNID='KLINTON' AND docdate > '$four_months' GROUP BY sales_info.custnmbr 	
) b on a.customer_number = b.custnmbr
where b.sales_info_id is null

Select allOpen in new window

We are getting close..When I ran the query this is what happened.

It displayed all the rows that are between $four_months and now.
It is a custmnbr has at leas one date older than four months ago it did not display the customer


I need to only display customers that MAX(docdate) is > $four_months
I think we are getting closer...

did you try inverting to <=
docdate <= '$four_months'
?

select a.* from (
	SELECT 
		customer_info.id                                                  AS customer_info_id,
		customer_info.customer_number                                     AS customer_number,
		customer_info.customersalespersonid                               AS customersalespersonid
	FROM customer_info
	WHERE customersalespersonid='KLINTON'
) as a
left join (
	SELECT 
		sales_info.id                                             AS sales_info_id,
		MAX(sales_info.docdate)                                   AS docdate,
		sales_info.custnmbr                                       AS custnmbr
	FROM sales_info
	WHERE SLPRSNID='KLINTON' AND docdate <= '$four_months' GROUP BY sales_info.custnmbr 	
) b on a.customer_number = b.custnmbr
where b.sales_info_id is null

Select allOpen in new window

Yes I did try to change the >= to <=..The issue is not with that, it still has to do with the where statement.

For instance If I have a stor # 100...And I have three sales

1. 01-01-2009
2. 01-03-2010
3. 08-01-2010

Even though I have two sales that are greater than $four_months I have one sale that is < $four_months. Therefore, I would not display this store because there is at least one sales that has been within the last four months...

With the current query if a store has at least one sale > $four_months it does not display it...thanks

Maybe we could do it this way, the query above kinda works but not exactly..

How about this, could you write a query blending the two querys below. But when you do that some how all we need is a where statement to display the stores that  "DO NOT" equal the store name..

Basically We find all the store names in the customer_info table then we find all the store names that have sales within the last four months, we then add a statement in the where statement that says some thing like

customer_info.customer_number <> sales_info.custnmbr


Is that a possibility..I think we are getting closer though...thanks for all the help..I really appreciate it so far..

After doing some reseach, your query is definately correct. I realized something in my db didnt match..thank you for your help