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

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
1 1519
jas2803
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.

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.