Hi all,
If I have a table like the following: (Table Name is A)
ID Date Time DeviceNumber Sum
1 1/12/2004 06:00:00 1 200
2 1/12/2004 08:00:00 1 600
3 1/12/2004 06:00:00 2 300
4 1/12/2004 08:00:00 2 800

If I write the following SQL statment:
"Select Eight.Time,Eight.DeviceNumber,Eight.Sum - Six.Sum As Dif
From A as Eight Inner Join A as six On Eight.ID=Six.ID
Where (Eight.Time = "08:00:00") And (Six.Time="06:00:00");

How SQL interpret this statment. I make the same table innered joined be ID field.
By purpose is to calculate the Difference between the Sum of the different hourse of the two devices.
I mean,
'06:00:00 - 08:00:00' 1 400 ---> (600-200)
'06:00:00 - 08:00:00' 2 500 ---> (800-300)

Please help me ti under stand and solce the problem.

Looks look you shoudl be joining on DeviceNumber and not on ID.

Hi,
I don't understand right now, How SQL pull out data according to the where criteria.
This will help me to design the query.
Here I thought to make two copies of the same table, and substract the Sum of 06:00:00 o'clock from 08:00: o'clock.

Can you help me to correct the statment.
thanks...

What I meant was:

Select Eight.Time,Eight.DeviceNumber,Eight.Sum - Six.Sum As Dif
From A as Eight Inner Join A as six On Eight.DeviceNumber=Six.DeviceNumber
Where (Eight.Time = "08:00:00") And (Six.Time="06:00:00");