View with WHERE clause in sub-query-CodePudding

I'm looking to create a view to retrieve list of employees and last attended training as at a specific date. Currently, we are having following T-SQL statement:

 ------------ ---------------- ------------ ----------- 
| employeeNo | trainingCourse | dateStart  | timeStart | 
 ------------ ---------------- ------------ ----------- 
| 0001       | MSSQL          | 2020-02-28 | 0800      | 
| 0001       | MSOffice       | 2020-09-30 | 1430      | 
| 0001       | DotNET         | 2021-06-03 | 1000      | 
| 0002       | MSSQL          | 2020-02-28 | 0800      | 
| 0003       | MSSQL          | 2020-02-28 | 0800      | 
| 0003       | WINDOWS        | 2021-03-07 | 2130      | 
 ------------ ---------------- ------------ ----------- 

SELECT *
FROM (
    SELECT t.*
        , RANK() OVER (PARTITION BY employeeNo ORDER BY dateStart DESC, timeStart desc) rn 
    FROM Training t
    WHERE dateStart <= @myPara
) v
WHERE rn = 1

Hence, any idea or possibility to make it as a View? Example:

SELECT * FROM myView WHERE dateStart <= '2020-12-31'

CodePudding user response：

Create a function that returns a table so that you pass it the parameter you want.

Here I use an inline table valued function and its creation is like creating a view, but you can pass it parameters.

Try this

use MyDate
GO

Create FUNCTION GetLastCourse
(    
   @DateStart Date
)
RETURNS TABLE 
AS
RETURN 
(
SELECT id ,employeeNo ,trainingCourse ,dateStart ,timeStart , rn
    FROM (
        SELECT t.id ,t.employeeNo ,t.trainingCourse ,t.dateStart ,t.timeStart
            , RANK() OVER (PARTITION BY employeeNo ORDER BY dateStart DESC, 
        timeStart desc) rn 
        FROM Training t
        WHERE dateStart <= @DateStart
    ) v
    WHERE rn = 1 
)
GO

see https://database.guide/create-an-inline-table-valued-function-itvf-in-sql-server/

Note: Don't use * in views or functions and always return a column id.

Usage

SELECT * FROM [dbo].[GetLastCourse] ( '2020-12-31' )

CodePudding user response：

I think for the above you may prefer a stored procedure over a view. You can have the date as input parameter.