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!

hi,

weiyn wrote:
> 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 <--

hi,

weiyn wrote:
> 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 <--

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

"Stefan Hoffmann" <stefan.hoffmann@explido.de>
??????:%23dikUdGUIHA.5516@TK2MSFTNGP02.phx.gbl...
> hi,
>
> weiyn wrote:
>> 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 <--

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

"Stefan Hoffmann" <stefan.hoffmann@explido.de>
??????:%23dikUdGUIHA.5516@TK2MSFTNGP02.phx.gbl...
> hi,
>
> weiyn wrote:
>> 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 <--

hi,

weiyn wrote:
> 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 <--

hi,

weiyn wrote:
> 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 <--