Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2010
    Posts
    12

    Unanswered: how to write query?

    Can Anyone please help me on this query!

    I have a table with the following structure

    -----------------------------------------------------------------
    | Id | AreaId | Object | Value | timestamp |
    -----------------------------------------------------------------
    1 Area10 'C2 Level' 600 2010-10-31 11:00
    2 Area10 c3 level 10 2010-10-31 11:00
    3 Area10 C2 Level 60 2010-10-31 11:15
    4 Area10 c3 level 910 2010-10-31 11:15

    5 Area11 C2 Level 404 2010-10-31 11:00
    6 Area11 c3 level 911 2010-10-31 11:00
    7 Area11 C2 Level 500 2010-10-31 11:15
    8 Area11 c3 level 900 2010-10-31 11:15

    9 Area12 C2 Level 608 2010-10-31 11:00
    10 Area12 c3 level 900 2010-10-31 11:00
    11 Area12 C2 Level 608 2010-10-31 11:15
    12 Area12 c3 level 900 2010-10-31 11:15

    I want to write a query that will give me something a result like

    ----------------------------------------------
    | AreaId | Result | timestamp |
    ----------------------------------------------
    Area10 ###### 2010-10-31 11:00
    Area10 ###### 2010-10-31 11:15

    Area11 ###### 2010-10-31 11:00
    Area11 ###### 2010-10-31 11:15

    Area12 ###### 2010-10-31 11:00
    Area12 ###### 2010-10-31 11:15


    Where,
    result is obtained by dividing "c2 level" by "c3 level"
    Timestamp is the unique time the c2 and c3 levels are monitored.

    I am completely stuck here and even have no idea how to proceed. Please help!!!!
    Last edited by sysenm; 11-07-10 at 07:55.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT one.AreaId
         , one.Value / two.Value AS result
         , one.timestamp
      FROM daTable AS one
    INNER
      JOIN daTable AS two
        ON two.AreaId = one.AreaId
       AND two.timestamp = one.timestamp
     WHERE one.Object = 'C2 level'
       AND two.Object = 'C3 level'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2010
    Posts
    12
    WOW Great .. .
    Thanks you very much ! You're a genius

Posting Permissions

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