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
开发者_如何学Go 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?
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.
精彩评论