Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    128

    Unanswered: calculations in a self-join (was "Query")

    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.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Looks look you shoudl be joining on DeviceNumber and not on ID.

  3. #3
    Join Date
    Mar 2004
    Posts
    128

    SQL query

    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...

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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");

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •