Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>A recursive CTE solution - assumes that each <code>Vehicle_No</code> appears in the table only once:</p> <pre><code>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 </code></pre> <p><strong>EDIT</strong> Corrected version of OP's query - note that it's not necessary to fully qualify the column names:</p> <pre><code>;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 </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POSQL Server: calculate field data from fields in same table but different set of data
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USEd Harper
    1. USEd Harper
    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. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. CO+1 for the recursive CTE
      singulars
      1. PO
      1. USRaj More
    2. COThanks for the help.. I´m still trying to implement and will let you know once done.
      singulars
      1. PO
      1. UStfp
    3. COOk so I tried to implement this but run into following problem: the moment I run this, it seems to disconnect from the database server the table is in and I get "Could not find server 'databasename' in sys.servers. What am I doing wrong here?
      singulars
      1. PO
      1. UStfp
 

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