无法在SQL Server 2008中获得正确的上一行和下一行

由于SQL Server 2008不支持LEAD / LAG功能，请尝试以下操作：

SELECT
  c.Trans_No, c.Trans_By,
  COALESCE(p.Trans_Date, c.Start_Date) AS Trans_Start_Date,
  COALESCE(p.Transact, 'Initiated') AS Transaction_Start,
  c.Trans_Date AS Trans_End_Date, c.Transact AS Transaction_End
FROM My_table AS c
OUTER APPLY (
  SELECT TOP(1) *
  FROM My_table AS p
  WHERE p.Trans_No = c.Trans_No AND p.Trans_Date < c.Trans_Date
  ORDER BY p.Trans_No, p.Trans_Date DESC
) AS p
ORDER BY Trans_No, Trans_Start_Date;

输出：

+----------+----------+------------------+-------------------+----------------+-----------------+
| Trans_No | Trans_By | Trans_Start_Date | Transaction_Start | Trans_End_Date | Transaction_End |
+----------+----------+------------------+-------------------+----------------+-----------------+
|      101 | Smith    | 2019-01-02       | Initiated         | 2019-01-03     | Processed       |
|      101 | Mary     | 2019-01-03       | Processed         | 2019-01-06     | Finalised       |
|      102 | Donald   | 2019-01-09       | Initiated         | 2019-01-10     | Processed       |
|      102 | Tony     | 2019-01-10       | Processed         | 2019-01-11     | Rejected        |
|      102 | Jennifer | 2019-01-11       | Rejected          | 2019-01-13     | Corrected       |
|      102 | George   | 2019-01-13       | Corrected         | 2019-01-17     | Finalised       |
+----------+----------+------------------+-------------------+----------------+-----------------+

使用SQL Fiddle在线进行测试。

更新：

WITH
  a AS (
    SELECT
      c.Trans_No, c.Trans_By, p.Trans_By AS Prev_Trans_By,
      COALESCE(p.Trans_Date, c.Start_Date) AS Trans_Start_Date,
      ca.pad + COALESCE(p.Transact, 'Initiated') AS Trans_Start,
      c.Trans_Date AS Trans_End_Date,
      ca.pad + c.Transact AS Trans_End
    FROM My_table AS c
    CROSS APPLY (
      VALUES(STR(DATEDIFF(DAY, 0, c.Trans_Date), 5))
    ) AS ca(pad)
    OUTER APPLY (
      SELECT TOP(1) *
      FROM My_table AS p
      WHERE p.Trans_No = c.Trans_No AND p.Trans_Date < c.Trans_Date
      ORDER BY p.Trans_No, p.Trans_Date DESC
    ) AS p
  )
SELECT
  a.Trans_No,
  a.Trans_By,
  MIN(a.Trans_Start_Date) AS Trans_Start_Date,
  RIGHT(MIN(a.Trans_Start),
        LEN(MIN(a.Trans_Start)) - 5) AS Trans_Start,
  MAX(a.Trans_End_Date) AS Trans_End_Date,
  RIGHT(MAX(a.Trans_End), LEN(MAX(a.Trans_End)) - 5) AS Trans_End
FROM a
CROSS APPLY (
  SELECT
    SUM(CASE
          WHEN s.Prev_Trans_By IS NULL OR
               s.Prev_Trans_By != s.Trans_By THEN 1
          ELSE 0
        END)
  FROM a AS s
  WHERE s.Trans_No = a.Trans_No AND
        s.Trans_Start_Date <= a.Trans_Start_Date
) AS ca(g)
GROUP BY a.Trans_No, a.Trans_By, ca.g
ORDER BY Trans_No, Trans_Start_Date;

输出：

+----------+----------+------------------+-------------+----------------+-----------+
| Trans_No | Trans_By | Trans_Start_Date | Trans_Start | Trans_End_Date | Trans_End |
+----------+----------+------------------+-------------+----------------+-----------+
|      101 | Smith    | 2019-01-02       | Initiated   | 2019-01-03     | Processed |
|      101 | Mary     | 2019-01-03       | Processed   | 2019-01-06     | Finalised |
|      102 | Donald   | 2019-01-09       | Initiated   | 2019-01-10     | Processed |
|      102 | Tony     | 2019-01-10       | Processed   | 2019-01-11     | Rejected  |
|      102 | Jennifer | 2019-01-11       | Rejected    | 2019-01-13     | Corrected |
|      102 | George   | 2019-01-13       | Corrected   | 2019-01-17     | Finalised |
|      105 | Tony     | 2019-03-01       | Initiated   | 2019-03-05     | Corrected |
|      105 | Smith    | 2019-03-05       | Corrected   | 2019-03-07     | Corrected |
|      105 | Jennifer | 2019-03-07       | Corrected   | 2019-03-08     | Finalised |
+----------+----------+------------------+-------------+----------------+-----------+

使用SQL Fiddle在线进行测试。