Attach table function in query

Question

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

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

For each user ID, it generates several entries. I need only the last entry for each empiricist who does not have the rank of the 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 entry and 4th entry you must select.

Answer 1

In SQL Server 2005+, you can use this Common Table Expression ( CTE ) to determine the last MonitorDate record that does not have a "Term" rank:

 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 must end in a colon. Because of this, I saw how many people write them as ;WITH EmployeeData AS...

Answer 2

You will have to play with this. You are having trouble mocking your sqlfiddle schema.

 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 check that left newcomers are at higher dates. How it works, you do the left outer. Each EXCEPT line for each user has a line with a higher monitoring date. This one line is the one you want. I usually use an example from my code, but I am on the wrong laptop. for its work you can choose foo., bar. and look at the results and find the desired line and make the condition correct.

You can also make it 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 it works. You can also configure it by caching the results of your union of TVF into a table variable.

EDIT: http://www.sqlfiddle.com/#!3/613e4/17 - I mocked your TVF here. Apparently sqlfiddle did not like the "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