Here is the Window function version Jeff mentioned

😎

SELECT

M.ORDERNO

,ROW_NUMBER() OVER

(

PARTITION BY M.ORDERNO

ORDER BY M.LASTUPDATEDDATE ASC

) AS ORDERNO_RID

,M.LASTUPDATEDDATE

,M.ORDERCLIENTINITIALFEE

,M.CLIENTINITFEE

,M.INITIALVENDORFEE

,M.VENDORFEE

,CASE

WHEN (LAG(M.VENDORFEE,1,0) OVER

(

PARTITION BY M.ORDERNO

ORDER BY M.LASTUPDATEDDATE ASC

)) = 0 THEN (M.VENDORFEE - M.INITIALVENDORFEE)

ELSE M.VENDORFEE - (LAG(M.VENDORFEE,1,0) OVER

(

PARTITION BY M.ORDERNO

ORDER BY M.LASTUPDATEDDATE ASC

))

END AS OUTPUT1

FROM dbo.MAIN M;

Results

ORDERNO ORDERNO_RID LASTUPDATEDDATE ORDERCLIENTINITIALFEE CLIENTINITFEE INITIALVENDORFEE VENDORFEE OUTPUT1

-------- ------------ --------------- ---------------------- -------------- ----------------- ---------- -------

1000 1 2014-01-01 3000 1000 700 1500 800

1000 2 2014-03-05 1000 2000 650 200 -1300

1000 3 2014-05-10 500 5000 375 125 -75

1000 4 2014-08-20 100 3500 675 1300 1175

1000 5 2014-11-20 100 2000 400 300 -1000

2000 1 2014-03-10 100 2000 375 125 -250

2000 2 2014-04-27 300 2000 675 1300 1175

2000 3 2014-09-20 200 2000 400 300 -1000

3000 1 2014-03-17 100 100 375 110 -265

3000 2 2014-03-22 500 200 450 380 270

4000 1 2014-01-01 300 400 500 600 100

5000 1 2014-01-01 100 250 375 750 375

6000 1 2014-04-23 300 300 120 190 70