David11011
asked on
Filter by highest value
I'm trying to find the last ship date for each customer in our database. Each customer has multiple orders in the orders table and I just need to see the last order that each customer made. Here is an example:
Imagine that I have a table like this.
CustNum ShipDate
123 08-01-2011
456 08-01-2011
789 08-03-2011
123 08-04-2011
123 08-12-2011
789 08-09-2011
I want my query results to look like this
CustNum ShipDate
123 08-12-2011
456 08-01-2011
789 08-09-2011
Imagine that I have a table like this.
CustNum ShipDate
123 08-01-2011
456 08-01-2011
789 08-03-2011
123 08-04-2011
123 08-12-2011
789 08-09-2011
I want my query results to look like this
CustNum ShipDate
123 08-12-2011
456 08-01-2011
789 08-09-2011
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Something like this should work.
Select CustNum,
(SELECT Max(SUB_TAB.ShipDate) FROM YourTable SUB_TAB WHERE SUB_TAB.CUSTOMERID = YourTable.CUSTOMERID) LAST_SHIP_DATE,
ShipDate ,
*
From YourTable
Select CustNum,
(SELECT Max(SUB_TAB.ShipDate) FROM YourTable SUB_TAB WHERE SUB_TAB.CUSTOMERID = YourTable.CUSTOMERID) LAST_SHIP_DATE,
ShipDate ,
*
From YourTable
Got it. Please try the following:
Select CUSTNO, DAYPHONE, COMPANYNAME, STATE, CUSTOMERTYPE, STATUS, FNAME, LNAME, FAXPHONE, SHIPDATE
From YourTable c
Inner Join (Select CUSTNO, Max(SHIPDATE) SHIPDATE From YourTable Group By CUSTNO) sdt
On sdt.CUSTNO = c.CUSTNO and sdt.SHIPDATE = c.SHIPDATE
Select CustNum,
(SELECT Max(SUB_TAB.ShipDate) FROM YourTable SUB_TAB WHERE SUB_TAB.CustNum = YourTable.CustNum) LAST_SHIP_DATE,
ShipDate ,
*
From YourTable
(SELECT Max(SUB_TAB.ShipDate) FROM YourTable SUB_TAB WHERE SUB_TAB.CustNum = YourTable.CustNum) LAST_SHIP_DATE,
ShipDate ,
*
From YourTable
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for all your help guys.
I realized that the customer information is consistent throughout the table so I could add all the columns in to the group by clause. Here is the query that I came up with.
I realized that the customer information is consistent throughout the table so I could add all the columns in to the group by clause. Here is the query that I came up with.
select CUSTOMERS.CUSTNO as 'CustomerNo' ,
CUSTOMERPHONE.DAYPHONE as 'Phone',
CUSTOMERS.NAMEX as 'Company',
CUSTOMERS.[STATE] as 'ST',
CUSTOMERS.CUSTTYPE as 'CT',
CUSTOMERS.CUSTSTATUS as 'CS',
CUSTOMERS.FNAME as 'First Name',
CUSTOMERS.LNAME as 'Last Name',
CUSTOMERPHONE.FAXPHONE as 'Fax',
max(macsaleUnion.SHIPDATE) as 'Last Ship Date'
from ORDERHEADER
inner join macsaleUnion on macsaleUnion.ORDERNO = substring(ORDERHEADER.FULLORDERNO, 0, 9)
inner join CUSTOMERS on ORDERHEADER.CUSTEDP = CUSTOMERS.CUSTEDP
full outer join CUSTOMERPHONE on ORDERHEADER.CUSTEDP = CUSTOMERPHONE.CUSTEDP
full outer join ITEMMAST on ITEMMAST.EDPNO = macsaleUnion.edpNos
where (ITEMMAST.ITEMNO like @itemNo + '%')
and (FAXPHONE <> '')
and (FAXPHONE <> '0')
and (FAXPHONE <> '1')
and (ISNUMERIC(FAXPHONE) = 1)
and (LEN(FAXPHONE) <= 10)
and (CUSTTYPE = 'TK' or CUSTTYPE = 'CT' or CUSTTYPE = 'PC' or CUSTTYPE = 'CO')
and (CUSTSTATUS = 'CC' or CUSTSTATUS = 'CA' or CUSTSTATUS = 'CB' or CUSTSTATUS = 'CV' or CUSTSTATUS = 'e%' or CUSTSTATUS = 'r%')
and ((cast(ORDERHEADER.ENTRYDATE as date) >= @startDate and cast(ORDERHEADER.ENTRYDATE as date) <= @endDate) or (cast(macsaleUnion.SHIPDATE as date) >= @startDate and cast(macsaleUnion.SHIPDATE as date) < @endDate))
group by CUSTOMERS.CUSTNO, CUSTOMERPHONE.DAYPHONE, CUSTOMERS.NAMEX, CUSTOMERS.[STATE], CUSTOMERS.CUSTTYPE, CUSTOMERS.CUSTSTATUS,CUSTOMERS.FNAME, CUSTOMERS.LNAME, CUSTOMERPHONE.FAXPHONE
ASKER
CUSTNO
DAYPHONE
COMPANYNAME
STATE
CUSTOMERTYPE
STATUS
FNAME
LNAME
FAXPHONE
SHIPDATE
The problem I'm having with the group by clause is that it will return a new row if there is different data in any of the columns.
I want to group by CUSTNO and not include the other columns in the group by clause so that rows will be dropped regardless of whether or not the other columns are unique.
I was having a hard aticulating my point. I hope that I wasn't too confusing