asked on

Query help needed

I need help with the logic of this query.

I have two tables - customers and orders.

Customers
--------------------------------------
custid name
1 Joe
2 Sam
3 Rick

Orders
-------------------------------------
orderid custid orderdate status
1 1 01/01/2006 posted
2 1 10/10/2013 posted
3 1 01/14/2014 in process
4 2 02/02/2008 posted

I need the query to select all customers where the orderdate =>01/01/2012 AND the status is posted.

The criteria for the expected customer results is based on the values in orders. Currently the results I am getting include duplication of customers where there are more than one order that meets the criteria.

Example:
SELECT * from Customers
INNER JOIN -- have tried other joins also
custid on customer.custid = orders.custid
WHERE
status = 'posted' and orderdate >= 01/01/2012

This results in two duplicate records in the results for custid 1

How do I need to properly construct the query to get the desired results?

mbizup

What database platform are you using?

Access? SQL Server? ...? (this makes a difference)

Try delimiting your dates:

status = 'posted' and orderdate >= #01/01/2012# (MS Access)

status = 'posted' and orderdate >= '01/01/2012' (SQL Server and others)

(Without the delimiters, 01/01/2012 is 1 divided by 1 divided by 2012)

ASKER CERTIFIED SOLUTION

Kent Olsen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ckelsoe

ASKER

Thanks - I think that should work perfectly for what I need.

ckelsoe

ASKER

Ok I now have this issue. The MAX(OrderID) does not work as there is a case where the highest order id does not have the date within the scope. An earlier orderid does have a date within scope. The code as written above drops that customer when it should be included in the results. I tried changing it to max(order_date) but that returns no results at all. Ideas?

ckelsoe

ASKER

So this works:

SELECT
	MAX (PostedDate)
FROM
	ERCHistory
WHERE
	customerid = 394
AND ERCStatus = 'posted'

Open in new window

But this does not:

SELECT
	c.CustomerID,
	c.Business,
	c.BusName,
	c.NameLast,
	c.NameFirst,
	c.Address1,
	c.City,
	c.State,
	c.Zip,
	c.Phone,
	c.BusPhone,
	c.Cell,
	c.Email,
	c.Pager,
	c.ResaleNumber,
	c.TaxExemptParts,
	c.TaxExemptLabor,
	c.[TimeStamp]
FROM
	dbo.Customer c
INNER JOIN dbo.ERCHistory o ON c.CustomerID = o.CustomerID
WHERE
	o.ERCStatus = 'Posted'
AND o.PostedDate >= '01/01/2006'
AND o.ERCID = (
	SELECT
		MAX (PostedDate)
	FROM
		ERCHistory o1
	WHERE
		o1.CustomerID = o.CustomerID
	AND o1.ERCStatus = 'posted'
)
ORDER BY c.CustomerID ASC

Open in new window

The initial code at MAX (ERCID) which did work.

Here is the data that is causing the issue:
ERCID      CustomerID      ERCStatus      PostedDate
913        394      Posted      2004-09-15-00:00:00.000
1064      394      Posted      2005-03-09-00:00:00.000
1103      394      Posted      2005-04-26-00:00:00.000
1281      394      Posted      2006-01-10-00:00:00.000
1282      394      Posted      2005-12-30-00:00:00.000

Kent Olsen

No problem. With just need to include the data check in the inner filter. I had assumed that the dates would increment as the ID value did...

SELECT *
FROM customers c
INNER JOIN order o
ON c.customer_id = o.customer_id
WHERE order_date >= '01/01/2013'
AND order_status = 'posted'
AND order_id = (select max(order_id) from order o1 where o1.customer_id = o.customer_id and o1.order_status = 'posted' and order_date >= '01/01/2013')

Try that one. :)

ckelsoe

ASKER

Ok It is still not picking up the cases where the highest ercid does not have the latest posteddate. Here is the modified code looks like this:

SELECT
	c.CustomerID,
	c.Business,
	c.BusName,
	c.NameLast,
	c.NameFirst,
	c.Address1,
	c.City,
	c.State,
	c.Zip,
	c.Phone,
	c.BusPhone,
	c.Cell,
	c.Email,
	c.Pager,
	c.ResaleNumber,
	c.TaxExemptParts,
	c.TaxExemptLabor,
	c.[TimeStamp]
FROM
	dbo.Customer c
INNER JOIN dbo.ERCHistory o ON c.CustomerID = o.CustomerID
WHERE
	o.ERCStatus = 'Posted'
AND o.PostedDate >= '01/01/2006'
AND o.ERCID = (
	SELECT
		MAX (ERCID)
	FROM
		ERCHistory o1
	WHERE
		o1.CustomerID = o.CustomerID
	AND o1.ERCStatus = 'posted'
  AND o.PostedDate >= '01/01/2006'
)
ORDER BY c.CustomerID ASC

Open in new window

Kent Olsen

Line 34.

It should read,

AND o1.PostedDate >= '01/01/2006'

ckelsoe

ASKER

Missed that - THANK YOU!!!!