Find the MAX(Date) and then find the next date <=MAX(Date)

[blueparaiba]

Hi I am trying to find the MAX(Date) in a table and then calculate the results from that row and then find the date that is equal or less than that date and calculate the results of that field. For example:

Ref Date Qty
6 29/09/01 2
6 02/08/01 3
6 01/07/02 1

23 28/09/01 2
23 01/08/01 3
23 01/07/01 5

The results show be:

6 29/09/01 Compare to the results in reference 23
23 28/09/01
Multiply qty 2*2

 

[simian336]

You could do something like this.

select date, ref, qty
from test
where ref=6
and date =(select Max(date)
from test
where ref=6)

union all

select top 1 date, ref, qty
from test
where ref=23 and date <= (select Max(date)
from test
where ref=6)

You could select the qty to a variable and multipy them... Why would you want to multipy quantities?

Simi