Alternative method for those without LEAD() and LAG()

WITH cteSAMPLEDATA

AS (--Create Sample data

SELECT ID, ORDER_ID, CODE, ORDER_DATE FROM

(VALUES

(1, 'ORDER1', 'DS', N'20151001'),

(2, 'ORDER2', 'DS', N'20151001'),

(5, 'ORDER2', 'DS', N'20151002'),

(3, 'ORDER3', 'DS', N'20151001'),

(6, 'ORDER3', 'AG', N'20151002'),

(8, 'ORDER3', 'AG', N'20151003'),

(4, 'ORDER4', 'DS', N'20151001'),

(7, 'ORDER4', 'AG', N'20151002'),

(9, 'ORDER4', 'DS', N'20151003')

)

AS MYTABLE(ID, ORDER_ID, CODE, ORDER_DATE)

)

SELECT A.ID

,A.ORDER_ID

,A.CODE

,A.ORDER_DATEAS ORDER_DATE

,B.IDAS PREVIOUS_ID

,B.CODEAS PREVIOUS_CODE

,B.ORDER_DATEAS PREVIOUS_ORDERDATE

FROM (

SELECT DISTINCT X.*

FROM cteSAMPLEDATA C

CROSS APPLY(

SELECT TOP 1 *

FROM cteSAMPLEDATA Z

WHERE Z.ORDER_ID = C.ORDER_ID

ORDER BY Z.ORDER_DATE DESC

) X

) A

CROSS APPLY(

SELECT TOP 1 *

FROM cteSAMPLEDATA Z

WHERE Z.ORDER_ID = A.ORDER_ID

AND Z.ORDER_DATE<A.ORDER_DATE

ORDER BY Z.ORDER_DATE DESC

) B

WHERE A.CODE<>B.CODE