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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.