Finding the difference between 2 specific rows...

Question

Hi

I need to find the difference between 2 specific rows but cannot hack the LAG/LEAD functions and not sure if these functions can help.

The row where dest=OM-OM_225 needs to be subtracted from the row where dest=OM-OM_20.

Using Oracle 9i.

Table is created using:

SELECT TRUNC(DATETIME,'HH') DATETIME,decode(OBJECT_ID,20,'OM-OM_20',225,'OM-OM_225',250,'OM-PSTN','OM-INT') DEST,

sum(BO) as CAABS,
SUM(BA+ RE) as CATT,
round(SUM((AC/22)*11,2) as CAMIN

FROM SCHEMA.TABLE
WHERE ((OBJECT_ID = 20) or  (OBJECT_ID = 225)  or (OBJECT_ID = 250) or (OBJECT_ID = 150) or (OBJECT_ID =160) or (OBJECT_ID = 161) or (OBJECT_ID = 162)  or (OBJECT_ID = 163) or (OBJECT_ID = 164) or (OBJECT_ID = 165) or(OBJECT_ID = 166)  or (OBJECT_ID = 167) ) 
and DATETIME between trunc(sysdate,'hh')-1/24 and trunc(sysdate,'hh')-1/24/3600
group by TRUNC(DATETIME,'HH'), decode(OBJECT_ID,20,'OM-OM_20',225,'OM-OM_225',250,'OM-PSTN','OM-INT')
order by 1 desc

OUTPUT

DATETIME                      DEST   CAABS           CATT          CAMINS

9/7/2009 1:00:00 PM OM-INT   10417           64670          87971.67
9/7/2009 1:00:00 PM OM-PSTN   7372           95388          13309.17
9/7/2009 1:00:00 PM OM-OM_20   6767             231884          184952.5
9/7/2009 1:00:00 PM OM-OM_225   33104           101003          68570.83

Answer 1

SELECT  SUM(DECODE(OBJECT_ID, 20, BO, -BO))
FROM    mytable
WHERE   OBJECT_ID IN (20, 225)
        AND DATETIME BETWEEN TRUNC(SYSDATE, 'HH') - 1 / 24 AND TRUNC(SYSDATE, 'hh') - 1 / 24 / 3600
GROUP BY
        TRUNC(DATETIME, 'HH')

Answer 2

If you want to do this in the same select, you can use joins:

select T1.some_field - coalesce(T2.some_field, 0),
...
from TABLE T1
left join TABLE T2 on T2.dest = 'OM-OM_225' and T1.dest = 'OM-OM_20'

(The concrete form of the query can be different depending on DB)