By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,843 Members | 2,300 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,843 IT Pros & Developers. It's quick & easy.

Date max

P: 30
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
Share this Question
Share on Google+
1 Reply


P: 30
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
FROM clients LEFT OUTER JOIN
(SELECT shipment.client, shipment.amount, shipment.comments, shipment.amountNumber
FROM shipment INNER JOIN
(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.