Join the table valued function in the query

Question

I have one table vwuser. I want join this table with the table valued function fnuserrank(userID). So I need to cross apply with table valued function:

SELECT *
FROM vwuser AS a
CROSS APPLY fnuserrank(a.userid)

For each userID it generates multiple records. I only want the last record for each empid that does not have a Rank of Term(inated). How can I do this?

Data:

HistoryID empid  Rank  MonitorDate
1          A1     E1    2012-8-9
2          A1     E2    2012-9-12 
3          A1     Term  2012-10-13
4          A2     E3     2011-10-09
5          A2     TERM   2012-11-9 

From this 2nd record and 4th record must be selected.

Answer 1

In SQL Server 2005+ you can use this Common Table Expression (CTE) to determine the latest record by MonitorDate that doesn't have a Rank of 'Term':

WITH EmployeeData AS
(
   SELECT *
      , ROW_NUMBER() OVER (PARTITION BY empId, ORDER BY MonitorDate DESC) AS RowNumber
   FROM vwuser AS a
   CROSS APPLY fnuserrank(a.userid)
   WHERE Rank != 'Term'
)
SELECT *
FROM EmployeeData AS ed
WHERE ed.RowNumber = 1;

Note: The statement before this CTE will need to end in a semi-colon. Because of this, I have seen many people write them like ;WITH EmployeeData AS...

Answer 2

You'll have to play with this. Having trouble mocking your schema on sqlfiddle.

Select bar.*
from 
(
SELECT *
FROM vwuser AS a
CROSS APPLY fnuserrank(a.userid)
where rank != 'TERM'
) foo
left join 
(
SELECT *
FROM vwuser AS b
CROSS APPLY fnuserrank(b.userid)
where rank != 'TERM'
) bar
on foo.empId = bar.empId
  and foo.MonitorDate > bar.MonitorDate
where bar.empid is null

I always need to test out left outers on dates being higher. The way it works is you do a left outer. Every row EXCEPT one per user has row(s) with a higher monitor date. That one row is the one you want. I usually use an example from my code, but i'm on the wrong laptop. to get it working you can select foo., bar. and look at the results and spot the row you want and make the condition correct.

You could also do this, which is easier to remember

SELECT *
FROM vwuser AS a
CROSS APPLY fnuserrank(a.userid)
) foo
join 
(
select empid, max(monitordate) maxdate
FROM vwuser AS b
CROSS APPLY fnuserrank(b.userid)
 where rank != 'TERM'
) bar
on foo.empid = bar.empid
and foo.monitordate = bar.maxdate

I usually prefer to use set based logic over aggregate functions, but whatever works. You can tweak it also by caching the results of your TVF join into a table variable.

EDIT: http://www.sqlfiddle.com/#!3/613e4/17 - I mocked up your TVF here. Apparently sqlfiddle didn't like "go".

select foo.*, bar.*
from 
(
SELECT f.*
FROM vwuser AS a
join fnuserrank f
  on a.empid = f.empid
where rank != 'TERM'
) foo
left join 
(
SELECT f1.empid [barempid], f1.monitordate [barmonitordate]
FROM vwuser AS b
join fnuserrank f1
  on b.empid = f1.empid
where rank != 'TERM'
) bar
on foo.empId = bar.barempid
  and foo.MonitorDate > bar.barmonitordate
where bar.barempid is  null