How To Get Different Jackets In Jibbin

With SQL CTE values from prior row determine

This example will show the problem. It shows a table with progressive consumption and hour meter.

value table

hours	consumption
a	1.8
2	4.6
3	6.8
4	7.7
5	9.0
6	12.3
7	14.8
8	15.9

Our interest is the calculation of consumption per hour. For this you have only one, the difference of fuel consumption calculated.

hours	consumption	minus	consumption line before	is	consumption per hour
a	1.8	-	0.0	=	1.8
2	4.6	-	1,8	=	2,8
3	6,8	-	4,6	=	2,2
5	7,7	-	6,8	=	0,9
6	9,0	-	7,7	=	1,3
7	12,3	-	9,0	=	3.3
8	14.8	-	12.3	=	2.5
9	15.9	-	14.8	=	1.1

how to do this with SQL? My first "reflex" a solution was a CURSOR in a stored procedure. This works, of course. Mexicans remember, however, that SQL is a query language is set, one can find a more elegant solution. We have two sets. Our main table and the amount of output table one row. JOIN We both quantity and subtract the fuel consumption.

How then do a JOIN on the line before?

Since SQL 2005 is the TSQL function ROW_NUMBER (). you are as an integer returns the number of the line. We can perform the necessary join on the line before by us in the second set the Row_Number return minus 1 :

amount of consumption column with Row_Number	amount of consumption column with Row_Number minus 1
consumption ROW_NUMBER 1.8 a 4,6 2 6,8 3 7,7 4 9,0 5 12,3 6 14.8 7 15.9 8	ROW_NUMBER - 1 consumption 1-1 = 0 1,8 2 -1 = 1 4,6 3 -1 = 2 6,8 4 -1 = 3 7,7 5 -1 = 4 9.0 6 -1 = 5 12.3 7 -1 = 6 14.8 8 -1 = 7 15.9

with ROW_NUMBER and ROW_NUMBER minus 1 gives the two sets for the join to the previous column:

consumption	ROW_NUMBER	ROW_NUMBER - 1	consumption
		0	1.8
1.8	a	1	4,6
4,6	2	2  	6,8
6,8	3	3	7,7
7,7	4	4	9,0
9,0	5	5	12,3
12,3	6  	6	14,8
14,8	7  	7  	15,9
15,9	8

with a CTE, we are now the result of the same issue in one go:

  1:   WITH  table of values (hours, consumption) 

  2:   AS  

  3 :  (

  4:   SELECT  hours = 1, consumption = 1.8  UNION  

  5:   SELECT  hours = 2, consumption = 4.6  UNION  

  6:   SELECT  Hours = 3, consumption = 6.8  UNION  

  7:   SELECT  hours = 4, consumption = 7.7  UNION  

  8:   SELECT  hours = 5, consumption = 9.0  UNION  

  9:   SELECT  hours = 6, consumption = 12.3  UNION  

  10:   SELECT  hours = 7, consumption = 14.8  UNION  

  11:   SELECT  hours = 8, Power = 9.15 

  12: ) 

  13: , set1 (No, hour, consumption) 

  14:   AS  

  15:  (

  16:   SELECT  

  17:  ROW_NUMBER ()  OVER  ( ORDER   BY  hours)  AS   'No' , 

  18:  hours, 

  19:  consumption 

  20:   FROM  value table 

  21: ) 

  22: , SET2 (NrMinusEins, hour, consumption) 

  23:   AS  

  24:  (

  25:   SELECT  

  26 :  (ROW_NUMBER ()  OVER  ( ORDER   BY  hours) - 1)  AS   'NrMinusEins' , 

  27:  hours, 

  28:  consumption 

  29:   FROM  value table 

  30: ) 

  31:  

  32:   SELECT  

  33:  Menge1.Betriebsstunden, 

  34:  Menge1.Verbrauch, 

  35:  Menge2.Verbrauch  AS   'consumption line before' , 

  36:  Menge2.Verbrauch - Menge1.Verbrauch  AS   'consumption per hour'  

  37:   FROM  

     38:      Menge1  

     39:      INNER     JOIN   Menge2   ON   Menge1.Nr = Menge2.NrMinusEins 

     40:      

result