Databases
StackOverflow2013
Postsdbo
POSQL Server: calculate field data from fields in same table but different set of data
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Server: calculate field data from fields in same table but different set of data
    primarykey
    data
    text
    <p>I was looking around and found no solution to this. I´d be glad if someone could help me out here:</p> <p>I have a table, e.g. that has among others, following columns: </p> <pre><code>Vehicle_No, Stop1_depTime, Segment_TravelTime, Stop_arrTime, Stop_Sequence </code></pre> <p>The data might look something like this:</p> <pre><code> 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 ... </code></pre> <p>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:</p> <pre><code>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 ... </code></pre> <p>I have tried to find a solution for some time but was not successful - Thanks for any help you can give me!</p> <hr> <p>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. </p> <p>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 - thanks a lot :)</p> <pre><code>;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 </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. UStfp
    1. UStfp
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL Server: calculate field data from fields in same table but different set of data
      1. USMahmoud Gamal
      1. VTFavorite
    1. COWhat is the primary key of your table? Does each `Vehicle_No` appear only once, or does `Vehicle_No` and `Stop1_DepTime` uniquely identify a trip (or is it some other column you haven't shown us)?
      singulars
      1. POSQL Server: calculate field data from fields in same table but different set of data
      1. USEd Harper
    2. COEach Vehicle_No appears only once as it also serves as trip number. Also: Thanks for the quick reply and solution - I am trying to implement it right now.
      singulars
      1. POSQL Server: calculate field data from fields in same table but different set of data
      1. UStfp
    3. COI've edited my answer to correct your version of the query. You don't need to fully qualify the column names when table aliases are being used (`recCTE as r`, `ZAEHL_2011.dbo.L32 AS t`)
      singulars
      1. POSQL Server: calculate field data from fields in same table but different set of data
      1. USEd Harper
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload