PARTITION BY duplicated id and JOIN with the ID with the least value

Question

I need to JOIN through a view in SQLServer 2008 tables hstT and hstD. The main table contains a data regarding employees and their "logins" (so multiple records associated to x employee in x month) and the second table has info about their area based on months, and I need to join both tables but keeping the earliest record as reference for the join and the rest of records associated to that id. So hstT its something like:

id id2 period   name
----------------------
x  1   0718     john
x  1   0818     john
y  2   0718     jane

And hstD:

id2 period   area
----------------------
1   0718     sales
1   0818     hr
2   0707     mng

With an OUTER JOIN I manage to merge all data based on ID2 (user id) and the period BUT as I mentioned I need to join the other table based on the earliest record by associating ID (which I could use as criteria) so it would look like this:

id id2 period   name area
---------------------------
x  1   0718     john sales
x  1   0818     john sales
y  2   0718     jane mng

I know I could use ROW_number but I don't know how to use it in a view and JOIN it on those conditions:

SELECT     T.*,D.*, ROW_NUMBER() OVER (PARTITION BY T.ID ORDER BY T.PERIOD ASC) AS ORID
FROM         dbo.hstT AS T LEFT OUTER JOIN
                      dbo.hstD AS D ON T.period = D.period AND T.id2 = D.id2
WHERE    ORID = 1
--prompts error as orid doesn't exist in any table

Answer 1

You can use apply for this:

select t.*, d.area
from hstT t outer apply
     (select top (1) d.*
      from hstD d
      where d.id2 = t.id2 and d.period <= t.period
      order by d.period asc
     ) d;

Actually, if you just want the earliest period, then you can filter and join:

select t.*, d.area
from hstT t left join
     (select d.*, row_number() over (partition by id2 order by period asc) as seqnum
      from hstD d
      order by d.period asc
     ) d;
     on d.id2 = t.id2 and seqnum = 1;