how to write query?

sysenm · #1 (**permalink**) 11-07-10, 06:47

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

r937 · #2 (**permalink**) 11-07-10, 07:06

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'

sysenm · #3 (**permalink**) 11-07-10, 07:35

WOW Great .. .
Thanks you very much ! You're a genius

Internet Services

Web Development

Digital Marketing

Consumer Tech