SQL Server: calculate field data from fields in same table but different set of data

I was looking around and found no solution to this. I´d be glad if someone could help me out here:

I have a table, e.g. that has among others, following columns:

Vehicle_No, Stop1_depTime, Segment_TravelTime, Stop_arrTime, Stop_Sequence

The data might look something like this:

    Vehicle_No  Stop1_DepTime   Segment_TravelTime  Stop_Sequence  Stop_arrTime
    201         13000           60                  1
    201         13000           45                  2
    201         13000           120                 3
    201         13000                               4
    202         13300           240                 1
    202         13300           60                  2
    ...

and I need to calculate the arrival time at each stop from the departure time at the first stop and the travel times in between for each vehicle. What I need in this case would look like this:

Vehicle_No  Stop1_DepTime   Segment_TravelTime  Stop_Sequence   Stop_arrTime
        201         13000       60                  1           
        201         13000       45                  2           13060
        201         13000       120                 3           13105
        201         13000                           4           13225
        202         13300       240                 1
        202         13300       60                  2           13540           
        ...

I have tried to find a solution for some time but was not successful - Thanks for any help you can give me!

Here is the query that still does not work - I am sure I did something wrong with getting the table from the database into this but dont know where. Sorry if this is a really simple error, I have just begun working with MSSQL.

Also, I have implemented the solution provided below and it works. At this point I mainly want to understand what went wrong here to learn about it. If it takes too much time, please do not bother with my question for too long. Otherwise - tha开发者_C百科nks a lot :)

;WITH recCTE
AS
(
    SELECT  ZAEHL_2011.dbo.L32.Zaehl_Fahrt_Id,  ZAEHL_2011.dbo.L32.PlanAbfahrtStart,  ZAEHL_2011.dbo.L32.Fahrzeit, ZAEHL_2011.dbo.L32.Sequenz,  ZAEHL_2011.dbo.L32.PlanAbfahrtStart AS Stop_arrTime
    FROM     ZAEHL_2011.dbo.L32
    WHERE    ZAEHL_2011.dbo.L32.Sequenz = 1

    UNION ALL

    SELECT t. ZAEHL_2011.dbo.L32.Zaehl_Fahrt_Id, t. ZAEHL_2011.dbo.L32.PlanAbfahrtStart, t. ZAEHL_2011.dbo.L32.Fahrzeit,t. ZAEHL_2011.dbo.L32.Sequenz, r.Stop_arrTime + r. ZAEHL_2011.dbo.L32.Fahrzeit  AS Stop_arrTime
    FROM    recCTE AS r
    JOIN     ZAEHL_2011.dbo.L32 AS t
    ON      t. ZAEHL_2011.dbo.L32.Zaehl_Fahrt_Id = r. ZAEHL_2011.dbo.L32.Zaehl_Fahrt_Id
    AND     t. ZAEHL_2011.dbo.L32.Sequenz = r. ZAEHL_2011.dbo.L32.Sequenz + 1
)
SELECT  ZAEHL_2011.dbo.L32.Zaehl_Fahrt_Id,  ZAEHL_2011.dbo.L32.PlanAbfahrtStart,  ZAEHL_2011.dbo.L32.Fahrzeit, ZAEHL_2011.dbo.L32.Sequenz,  ZAEHL_2011.dbo.L32.PlanAbfahrtStart,
CASE WHEN Stop_arrTime =  ZAEHL_2011.dbo.L32.PlanAbfahrtStart THEN NULL ELSE Stop_arrTime END AS Stop_arrTime
FROM recCTE
ORDER BY  ZAEHL_2011.dbo.L32.Zaehl_Fahrt_Id,  ZAEHL_2011.dbo.L32.Sequenz

A recursive CTE solution - assumes that each Vehicle_No appears in the table only once:

DECLARE @t TABLE
(Vehicle_No  INT
,Stop1_DepTime   INT
,Segment_TravelTime INT 
,Stop_Sequence  INT
,Stop_arrTime INT
)
INSERT @t (Vehicle_No,Stop1_DepTime,Segment_TravelTime,Stop_Sequence)
VALUES(201,13000,60,1),
(201,13000,45,2),
(201,13000,120,3),
(201,13000,NULL,4),
(202,13300,240,1),
(202,13300,60,2)


;WITH recCTE
AS
(
    SELECT Vehicle_No, Stop1_DepTime, Segment_TravelTime,Stop_Sequence, Stop1_DepTime AS Stop_arrTime
    FROM    @t
    WHERE   Stop_Sequence = 1

    UNION ALL

    SELECT t.Vehicle_No, t.Stop1_DepTime, t.Segment_TravelTime,t.Stop_Sequence, r.Stop_arrTime + r.Segment_TravelTime  AS Stop_arrTime
    FROM    recCTE AS r
    JOIN    @t AS t
    ON      t.Vehicle_No = r.Vehicle_No
    AND     t.Stop_Sequence = r.Stop_Sequence + 1
)
SELECT Vehicle_No, Stop1_DepTime, Segment_TravelTime,Stop_Sequence, Stop1_DepTime,
CASE WHEN Stop_arrTime = Stop1_DepTime THEN NULL ELSE Stop_arrTime END AS Stop_arrTime
FROM recCTE
ORDER BY Vehicle_No, Stop_Sequence

EDIT Corrected version of OP's query - note that it's not necessary to fully qualify the column names:

;WITH recCTE
AS
(
    SELECT  Zaehl_Fahrt_Id,  PlanAbfahrtStart,  Fahrzeit, L32.Sequenz,  PlanAbfahrtStart AS Stop_arrTime
    FROM     ZAEHL_2011.dbo.L32
    WHERE    Sequenz = 1

    UNION ALL

    SELECT t.Zaehl_Fahrt_Id, t.PlanAbfahrtStart, t.Fahrzeit,t.Sequenz, r.Stop_arrTime + r.Fahrzeit  AS Stop_arrTime
    FROM    recCTE AS r
    JOIN     ZAEHL_2011.dbo.L32 AS t
    ON      t.Zaehl_Fahrt_Id = r.Zaehl_Fahrt_Id
    AND     t.Sequenz = r.Sequenz + 1
)
SELECT  Zaehl_Fahrt_Id,  PlanAbfahrtStart,  Fahrzeit, Sequenz,  PlanAbfahrtStart,
CASE WHEN Stop_arrTime =  PlanAbfahrtStart THEN NULL ELSE Stop_arrTime END AS Stop_arrTime
FROM recCTE
ORDER BY  Zaehl_Fahrt_Id,  Sequenz

I'm quite sure this works:

SELECT a.Vehicle_No, a.Stop1_DepTime, 
       a.Segment_TravelTime, a.Stop_Sequence, a.Stop1_DepTime +
  (SELECT SUM(b.Segment_TravelTime) FROM your_table b 
   WHERE b.Vehicle_No = a.Vehicle_No AND b.Stop_Sequence < a.Stop_Sequence)
FROM your_table a
ORDER BY a.Vehicle_No