[SQL] Need help getting time between rows

[half flung pie]

I've almost got what I need already, except for one minor (yet important) detail.

I have a table

SELECT [ID_NUM]
      ,[HIST_STAGE]
      ,[JOB_TIME]
  FROM [STAGE_HISTORY_TRAN]
  WHERE [ID_NUM] = '460408'
  ORDER BY [JOB_TIME]

Which returns
460408 04 2008-02-15 15:58:12.433
460408 10 2008-08-28 08:54:58.953
460408 200 2008-09-29 23:18:13.243
460408 22 2009-03-27 11:40:41.160
460408 25 2009-09-11 14:05:31.867

I've written a view which gives me the time spent in each stage, but it doesn't quite finish.

WITH rows AS
	(
	SELECT *, ROW_NUMBER() OVER (ORDER BY [JOB_TIME]) as rn
	FROM [STAGE_HISTORY_TRAN] as sh
	WHERE ID_NUM = '460408'
	)
SELECT 
	mc.[ID_NUM], 
	mc.[HIST_STAGE] as [StageCode], 
	mc.[JOB_TIME] as [EnteredStage], 
	mp.[JOB_TIME] as [ExitedStage], 
	DATEDIFF(second, mc.[JOB_TIME], mp.[JOB_TIME]) as [SecondsInStage]
FROM rows as mc
JOIN rows as mp
ON mc.rn = mp.rn-1

Returns
460408 04 2008-02-15 15:58:12.433 2008-08-28 08:54:58.953 16822606
460408 10 2008-08-28 08:54:58.953 2008-09-29 23:18:13.243 2816595
460408 200 2008-09-29 23:18:13.243 2009-03-27 11:40:41.160 15423748
460408 22 2009-03-27 11:40:41.160 2009-09-11 14:05:31.867 14523890

Which looks good, except notice the final stage isn't being output by the view. I realize this final stage wouldn't have an ExitedStage entry or SecondsInStage, so they can either be NULL or show for GETDATE(), it doesn't really matter to me, as long as there is a row for that stage.

I tried to simply UNION a select of that table to the end of these results, selecting only one entry and ordering by desc, but apparently you can't 'order by' inside of a union piece.

Here's what I tried.

UNION (
SELECT TOP 1
	sht.[ID_NUM], 
	sht.[HIST_STAGE] as [StageCode], 
	sht.[JOB_TIME] as [EnteredStage], 
	GETDATE() as [ExitedStage], 
	DATEDIFF(second, sht.[JOB_TIME], GETDATE()) as [SecondsInStage]
FROM [STAGE_HISTORY_TRAN] as sht
WHERE sht.[ID_NUM] = '460408'
/*ORDER BY sht.[JOB_TIME] DESC*/
)

But since the ORDER BY fails, it appends the first row in sht to my results from above. That's, of course, not what I want.

Aside from simply creating another view with the order by statement built in, and then unioning that to my first statement, does anyone have any ideas on how to accomplish my goal?

To recap, this is what I want returned:
460408 04 2008-02-15 15:58:12.433 2008-08-28 08:54:58.953 16822606
460408 10 2008-08-28 08:54:58.953 2008-09-29 23:18:13.243 2816595
460408 200 2008-09-29 23:18:13.243 2009-03-27 11:40:41.160 15423748
460408 22 2009-03-27 11:40:41.160 2009-09-11 14:05:31.867 14523890
460408 25 2009-09-11 14:05:31.867 NULL NULL

[half flung pie]

Kind of forgot I was supposed to be making a view... so I had to restructure things. Eventually figured it out though.

ALTER VIEW [StageLengths]
AS
WITH rows AS
	(
	SELECT *, ROW_NUMBER() OVER (ORDER BY [ID_NUM], [JOB_TIME]) as rn
	FROM [STAGE_HISTORY_TRAN] as sh
	)
SELECT 
	mc.[ID_NUM], 
	mc.[HIST_STAGE] as [StageCode], 
	mc.[JOB_TIME] as [EnteredStage], 
	[ExitedStage] =
	CASE 
		WHEN mp.[JOB_TIME] < mc.[JOB_TIME] THEN NULL
		ELSE mp.[JOB_TIME]
	END,
	[SecondsInStage] = 
	CASE
		WHEN mp.[JOB_TIME] < mc.[JOB_TIME] THEN NULL
		ELSE DATEDIFF(second, mc.[JOB_TIME], mp.[JOB_TIME])
	END
FROM rows as mc
JOIN rows as mp
ON mc.rn = mp.rn-1

[techgnome]

IT should have been a left join rather than an inner join

-tg