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, 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, ORDER BY

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,
(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,, 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.