Solved

MAX(date) mysql not working

Posted on 2010-08-26
20
278 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
  • 11
  • 7
  • 2
20 Comments
 
LVL 41

Expert Comment

by:ralmada
Comment Utility
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
Comment Utility
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 51

Expert Comment

by:HainKurt
Comment Utility
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
 
LVL 41

Expert Comment

by:ralmada
Comment Utility
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
Comment Utility
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
Comment Utility
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 51

Expert Comment

by:HainKurt
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
>>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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:brad0525
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

771 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now