468,316 Members | 1,989 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,316 developers. It's quick & easy.

Date max

I have two tables,
active clients and shipments.
Shipments tables is like this
client, date, amount
the clients table is:
client, state, city
I use this to get the Max amount per client for a time period:

SELECT Max(monthly.amount) as max_amt, clients.client, clients.city FROM clients LEFT OUTER JOIN (SELECT client, date, amount FROM shipment WHERE "date" Between '11/30/08' and '1/1/09') monthly ON clients.client = monthly.client WHERE (clients.state = 'AZ') GROUP BY clients.client, clients.city ORDER BY clients.city

I am having difficulties now trying to find the last shipment and amount made for the same time period.

I thought I could use something similar to this, but seems I keep getting group by errors if I change anything to work with the date.

Any ideas?
Regards, JAS
Dec 15 '08 #1
1 1519
I used amountnumber (an auto generated number + 1 per shipment PK)
Not sure if I did this correctly but it looks like it is giving the right values.....

SELECT monthly.amount, monthly.Comments, clients.client, clients.state, clients.city
(SELECT shipment.client, shipment.amount, shipment.comments, shipment.amountNumber
(SELECT MAX(amountNumber) AS max_amountNumber, client
FROM shipment
WHERE (Date BETWEEN '10/31/08' AND '11/30/08')
GROUP BY client) amnt ON shipment.amountNumber = amnt.max_amountNumber) monthly ON
clients.client = monthly.client
WHERE (clients.state = 'AZ')
GROUP BY clients.client, clients.state, clients.city, monthly.amount, monthly.comments
ORDER BY clients.client
Dec 16 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by androtech | last post: by
4 posts views Thread by Richard Hollenbeck | last post: by
2 posts views Thread by Tjerk | last post: by
9 posts views Thread by Thomas R. Hummel | last post: by
30 posts views Thread by Dr John Stockton | last post: by
1 post views Thread by Liz Malcolm | last post: by
12 posts views Thread by Assimalyst | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.