Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cyborfool
Starting Member
2 Posts |
 Posted - 2010-09-22 : 16:20:18
|
| 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.EFFSEQFROM PS_JOB CWHERE 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?I know how to create an sp or a UDF, but my challenge is to create a report in Excel with this data that will still allow a user to edit / enter parameters through the GUI, so I don't think a proc or function will work.Thanks for any help! |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-09-22 : 17:14:07
|
| You cannot pass parameters to a view, so it will have to be done as a stored procedure. Change the GUI/Excel code to accept the parameters from the spreadsheet and pass them to the procedure. I've done this several times and it works, it's just a bit of VBA code. |
 |
|
|
cyborfool
Starting Member
2 Posts |
Posted - 2010-09-23 : 01:10:17
|
quote:
Originally posted by robvolk
You cannot pass parameters to a view, so it will have to be done as a stored procedure. Change the GUI/Excel code to accept the parameters from the spreadsheet and pass them to the procedure. I've done this several times and it works, it's just a bit of VBA code.
Thanks robvolk! I've never written VBA code. Would you have one that you done in the past that I might use by way of example? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-09-23 : 09:34:21
|
| Check your email. If you didn't receive one from me, email your regular email address to me via SQLTeam. |
|
|
|
|
|
|
SQL Server 2005 View / Transact-SQL Question