Link to home
Start Free TrialLog in
Avatar of jxharding
jxharding

asked on

Northwind example:get max(qty) for a max(date) < @MyDate

hi,
i please need help to get the highest date for a [Order Details] for Northwind, where date earlier than @MyDate,
and then get the Max(Qty) for that date.
e.g.

-the complete sql query
select  * from dbo.Orders o inner join [Order Details] od on o.orderID = od.orderid
where orderdate < '1997/09/01'


-my attempt gets the Max(OrderDate), but not the max(Qty) for that orderDate
select EmployeeID,Max(OrderDate) as OrderDate from dbo.Orders o inner join [Order Details] od on o.orderID = od.orderid
where orderdate < '1997/09/01'
group by EmployeeID
so it should return
EmployeeID             OrderDate    MaxQty
3                              1997-08-25   21
6                               1997-08-25   21
9                                1997-08-27  35

please help me to get the MaxQty with the MaxDate



   
 
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial