Set DateTime = To DateTime Of previous row on a view-CodePudding

Below is the code to a view created to pull delimited values from a table into new rows in a view.

This works well, but I can't figure out how to set the TASDateTimeStart column value to the previous row's TASDateTimeEnd column value where the id in both rows are the same

SELECT
    MallaghanApp.dbo.EmployeeClockIn.Id,
    Employee,
    JobType,
    EmployeeName,
    EmployeeNumber,
    value AS SerialNumber,
    CAST([TASDateTimeEnd] AS smalldatetime) [TASDateTimeEnd],
    CAST([TASDateTimeStart] AS smalldatetime) [TASDateTimeStart],
    ISNULL(((SELECT DATEDIFF(MINUTE, TASDateTimeStart, TASDateTimeEnd)) / 
           ((SELECT LEN([SerialNo]) - LEN(REPLACE([SerialNo], ',', '')))   1)), (DATEDIFF(MINUTE, TASDateTimeStart, CURRENT_TIMESTAMP)) / ((SELECT LEN([SerialNo]) - LEN(REPLACE([SerialNo], ',', '')))   1)) AS MinutesClocked,
    Department,
    DepartmentNumber
FROM 
    EmployeeClockIn
CROSS APPLY 
    STRING_SPLIT([SerialNo], ',') 
LEFT JOIN 
    EmployeeInfo ON Employee = EmployeeCombinedInfo

I keep getting an error

Subquery returned more than 1 value.This is not permitted when the subquery follows =,!=,<,<=,>,>= or when the subquery is used as an expression

Does anyone know the code needed here?

CodePudding user response：

You can use the LAG function to get this value:

SELECT eci.Id, 
        eci.TASDateTimeEnd,
        LAG(eci.TASDateTimeEnd) OVER (PARTITION BY eci.Id ORDER BY eci.TASDateTimeEnd) as PreviousTASDateTimeEnd
            
FROM EmployeeClockIn eci;