Set DateTime = To DateTime Of prevuious row on a view

July 25, 2022

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

This works well, but i cant figure out how to set the TASDateTimeStart Column = to the previous rows TASDateTimeEnd Column 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 the error "Subquery returned more than 1 value.This is not permitted when the subquery follows =,!=,<,<=,>,>= or when the subquery is used as an expression"

Anyone know the code needed here?

>Solution :

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;