Solved

MAX(date) mysql not working

Posted on 2010-08-26
20
285 Views
Last Modified: 2013-12-13
Below is a query I am trying to use..But I need it to select the max date from sales_info.docdate..I keep getting errors if I try max(sales_info.docdate)..Thanks

Brad
$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,
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_number";

Open in new window

0
Comment
Question by:brad0525
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 7
  • 2
20 Comments
 
LVL 41

Expert Comment

by:ralmada
ID: 33535625
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";

Open in new window

0
 

Author Comment

by:brad0525
ID: 33535703
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..
0
 
LVL 56

Expert Comment

by:HainKurt
ID: 33535718
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????


0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 41

Expert Comment

by:ralmada
ID: 33535756
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)";

Open in new window

0
 

Author Comment

by:brad0525
ID: 33535801
it is taking a really long time to load, so i am not sure what the results are that it is returning...
0
 

Author Comment

by:brad0525
ID: 33535927
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...
0
 
LVL 56

Expert Comment

by:HainKurt
ID: 33536323
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...
0
 

Author Comment

by:brad0525
ID: 33540988
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
0
 

Author Comment

by:brad0525
ID: 33541362
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>";
}
?>

Open in new window

0
 
LVL 41

Expert Comment

by:ralmada
ID: 33542122
>>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.
 
0
 

Author Comment

by:brad0525
ID: 33542208
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.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 33542330
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

Open in new window

0
 

Author Comment

by:brad0525
ID: 33543446
When I run the query in my sql I get an error

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

Expert Comment

by:ralmada
ID: 33544052
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

Open in new window

0
 

Author Comment

by:brad0525
ID: 33545366
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...
0
 
LVL 41

Expert Comment

by:ralmada
ID: 33545483
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

Open in new window

0
 

Author Comment

by:brad0525
ID: 33549103
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
0
 
LVL 41

Accepted Solution

by:
ralmada earned 500 total points
ID: 33549703
well, maybe what you're looking for is using EXISTS
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
where not exists(SELECT 1	FROM sales_info
	WHERE SLPRSNID='KLINTON' AND docdate > '$four_months' and custnmbr = a.customer_number)

Open in new window

0
 

Author Comment

by:brad0525
ID: 33553178
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..
0
 

Author Closing Comment

by:brad0525
ID: 33553216
After doing some reseach, your query is definately correct. I realized something in my db didnt match..thank you for your help
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo‚Ķ
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
The viewer will learn how to count occurrences of each item in an array.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question