Current Previous information help needed !

[royalcheese]

I have a table with a the following layout

Fund_ID (KEY) Fund Date

1 4 1/1/2006
1 5 1/2/2006
1 -7 1/3/2006
2 2 1/2/2006
2 1 1/1/2006


I want a query to take a input date ( hard coded for testing at the min.)

and output the current value and the previous date eg

date searched 1/2/2006
Fund_ID current previous

1 5 4
2 2 1

but . . .

date searched 1/3/2006
Fund_ID current previous

1 -7 5
2 1 Null

As it has no values

Please can any one help as its 7 and wanna go home

 

[Remou]

I don't understand the problem. What do you want instead of Null? Null is the right answer, isn't it?

 

[Golom]

I'm a bit fuzzy about what you're after but is it something like this?

Select Fund_ID, C.Fund As [Current], P.Fund As [Previous]

From myTable As C INNER JOIN myTable As P
     ON C.Fund_ID = P.Fund_ID

WHERE 
      C.[Date] = (Select MAX([Date]) From myTable X
                  Where X.Fund_ID = C.Fund_ID
                    AND X.[Date] <= #1/3/2006#)

  AND P.[Date] = (Select MAX([Date]) From myTable X
                  Where X.Fund_ID = C.Fund_ID
                    AND X.[Date] < C.[Date])

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]