SQL Server 2005 Views

Question

I have a table that contains multiple records per employee by effective data and effective sequence: EMPLID, EFFDT, EFFSEQ.

I am trying to come up with a view in which I could specify an As Of Date other than the current date and get the most current record for each employee as of that date. The query that I usually use is:

SELECT C.EMPLID, C.EFFDT, C.EFFSEQ
  FROM PS_JOB C
 WHERE C.EFFSEQ = (SELECT MAX(INNERALIAS.EFFSEQ)
                     FROM PS_JOB INNERALIAS 
                    WHERE INNERALIAS.EMPLID = C.EMPLID
                      AND INNERALIAS.EFFDT = C.EFFDT)
   AND C.EFFDT = (SELECT MAX(INNERALIAS.EFFDT)
                    FROM PS_JOB INNERALIAS 
                   WHERE INNERALIAS.EMPLID = C.EMPLID
                     AND INNERALIAS.EFFDT <= GetDate())

Is there any way to create a view on SQL Server in which I can specify a date to compare the INNERALIAS.EFFDT to in the second subselect?

Answer 1

You can't parameterize a view, but you can leave your view as is and parameterize a sp that returns results from the view limited by your parameter and send the output of the sp to a table and use that table in Excel.