How i make the query

[weiyn]

hi!
How i make this query?
the table is follow:
id date_time kilowatt_hour
1 2006-10-20 1:00 51208
2 2006-10-20 2:00 52283
3 2006-10-20 3:00 53852
4 2006-10-20 4:00 55084
5 2006-10-20 5:00 56120

how i get the the change of kilowatt_hour.
for example:
id date_time kilowatt_hour change
1 2006-10-20 1:00 51200
2 2006-10-20 2:00 51300 100
3 2006-10-20 3:00 51800 500
4 2006-10-20 4:00 52500 700
5 2006-10-20 5:00 53000 500

thanks!

 

[Stefan Hoffmann]

hi,

How i make this query?
how i get the the change of kilowatt_hour.
for example:
id date_time kilowatt_hour change
1 2006-10-20 1:00 51200
2 2006-10-20 2:00 51300 100
3 2006-10-20 3:00 51800 500
4 2006-10-20 4:00 52500 700
5 2006-10-20 5:00 53000 500

You need to get the previous value by [date_time] or by [id] (if it
correlates to the date time value). This should work:

SELECT
t1.*,
(SELECT TOP 1 [kilowatt_hour]
FROM [yourTable] t2
WHERE t2.[id] < t1.[id]
ORDER BY t2.[id] DESC
) [previous_kilowatt_hour]
FROM [yourTable] t1


mfG
--> stefan <--

 

[weiyn]

thank you very much!
but i can't get the solution.there is error in the SQL.
may be 2 sentence must?

hi,

How i make this query?
how i get the the change of kilowatt_hour.
for example:
id date_time kilowatt_hour change
1 2006-10-20 1:00 51200
2 2006-10-20 2:00 51300 100
3 2006-10-20 3:00 51800 500
4 2006-10-20 4:00 52500 700
5 2006-10-20 5:00 53000 500

You need to get the previous value by [date_time] or by [id] (if it
correlates to the date time value). This should work:

SELECT
t1.*,
(SELECT TOP 1 [kilowatt_hour]
FROM [yourTable] t2
WHERE t2.[id] < t1.[id]
ORDER BY t2.[id] DESC
) [previous_kilowatt_hour]
FROM [yourTable] t1


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi,

thank you very much!
but i can't get the solution.there is error in the SQL.
may be 2 sentence must?

SELECT
t1.*,
(SELECT TOP 1 [kilowatt_hour]
FROM [yourTable] t2
WHERE t2.[id] < t1.[id]
ORDER BY t2.[id] DESC
) [previous_kilowatt_hour]

Insert the keyword "AS" between ) and [previous_kilowatt_hour]:

) AS [previous_kilowatt_hour]

FROM [yourTable] t1

mfG
--> stefan <--