Luis Cazares (7/2/2015)
I'm not sure about your desired results, but I believe that you need to add a partition to your window functions.
SELECT
S_ID,
LAG(S_ACTV_CODE, 1) OVER (PARTITION BY S_ID ORDER BY S_DATETIME ASC) AS S_ACTV_CODE_PREV,
S_ACTV_CODE,
S_USER,
S_DATETIME AS START_TIME,
LEAD(S_DATETIME, 1) OVER (PARTITION BY S_ID ORDER BY S_DATETIME ASC) AS END_TIME,
DATEDIFF (SECOND, S_DATETIME,
LEAD(S_DATETIME, 1) OVER (PARTITION BY S_ID ORDER BY S_DATETIME ASC)) AS DURATION
FROM (
SELECT
S_ID,
LAG(S_ACTV_CODE, 1, 'N/A') OVER (ORDER BY S_DATETIME ASC) AS S_ACTV_CODE_PREV,
S_ACTV_CODE,
S_USER,
S_DATETIME
FROM TABLE1
) X WHERE ISNULL(S_ACTV_CODE_PREV,'') != ISNULL(S_ACTV_CODE,'')
ORDER BY S_ID, S_DATETIME;
The results do not quite match what the OP posted as expected results.