sql server programming:
tbl1
fNum fNal fPum fIn fOut fStock
1 2006/05/06 p1 9 1 8
3 2006/05/07 p1 10 0 18
2 2006/05/07 p2 7 3 4
4 2006/05/08 p3 10 9 1
5 2006/05/10 p3 4 3 2
tbl2
fNal fPum
2006/05/08 p1
2006/05/08 p2
2006/05/08 p3
2006/05/09 p1
2006/05/09 p2
2006/05/09 p3
2006/05/10 p1
2006/05/10 p2
2006/05/10 p3

and then the 2006/05/08 <= fNal <= 2006/05/10 ,,

the result below
i want to make query. but i'don't know,,,,

fNal fPum fIn fOut fStock
2006/05/08 p1 0 0 18
2006/05/08 p2 0 0 4
2006/05/08 p3 10 9 1
2006/05/09 p1 0 0 18
2006/05/09 p2 0 0 4
2006/05/09 p3 0 0 2
2006/05/10 p1 0 0 18
2006/05/10 p2 0 0 4
2006/05/10 p3 4 3 2

thank you very much, Tom!
goodbye........

I'm guessing that the results you said you wanted have a typo and you wanted
fStock to be 1 for the 2006/06/07 date and fPum = p3 in your query result
given the input data you have. If that is so, then the following gives the
result you want,

Select x.fNal, x.fPum, x.fIn, x.fOut, s.fStock From
(Select Convert(varchar(10), t2.fNal, 111) 'fNal',
t2.fPum,
Coalesce(fIn,0) 'fIn',
Coalesce(fOut,0) 'fOut',
fStock,
(Select Max(m.fNal) From tbl1 m Where m.fPum = t2.fPum And m.FNal <=
t2.FNal) 'LastDate'
From tbl2 t2
Left Join tbl1 t1 On t1.fNal = t2.fNal And t1.fPum = t2.fPum
) x
Left Join tbl1 s On s.fNal = x.LastDate And s.fPum = x.fPum
Order By x.FNal, x.fPum

If you really did want fStock to be 2 for 2006/06/07 and fPum=p3 in the
result, could you tell us what formula or business rule you are using to get
that value?

Tom

[quoted text, click to view]