SQL & PL/SQL

Dear All,

I have a histall_temp table declared in my area as below -

desc histall_temp

Name                           Null     Type                                                                                                                                                                                          
------------------------------ -------- -------------------------------------
UNIT                                     VARCHAR2(50)                                                                                                                                                                                  
GROUP                                  VARCHAR2(50)                                                                                                                                                                                  
LOC                                      VARCHAR2(50)                                                                                                                                                                                  
STARTDATE                          DATE                                                                                                                                                                                          
TYPE                                    NUMBER                                                                                                                                                                                        
QTY                                     FLOAT(126)  

insert into histall_temp
values ('a',' ','b','9-26-2010',1,10);

insert into histall_temp
values ('a',' ','b','9-26-2010',2,2);

insert into histall_temp
values ('a',' ','b','9-26-2010',2,9);

insert into histall_temp
values ('a',' ','b','9-17-2012',1,0);

I am trying to compare the Qtys for Type 1 and Type 2 where we have the same Unit, group, loc and Startdate combination and to find out all the records where Sum(Qty) for type1s is lower than Sum(Qty) for type 2s.

Now, Type1s can not exist in duplication - meaning, at any point in time I will have only one Type 1 record for the same Unit, group, Loc and Startdate combination.
But, its just the opposite for Type 2s.
I may (or, may not) have more than one Type 2s, for the same Unit, group, loc and Startdate combination.

The second scenario makes my life more difficult.
I initially considered it will not be the case for Type 2s, so I used the following query to load up to my actual table as below - but this is wrong results.

INSERT /*+ APPEND */ INTO hist
 select h1.unit, h1.group, h1.loc, h1.startdate,10080,2,'NEG ADJ', (h1.qty - h2.qty), 'POS'
               from histall_temp h1, histall_temp h2
               where h1.unit = h2.unit
               and   h1.group = h2.group
               and h1.startdate = h2.startdate
               and h1.type = 1
               and h2.type = 2
               and (h1.qty - h2.qty) < 0
               group by h1.unit, h1.group, h1.loc, h1.startdate,2,'Neg Adj', (h1.qty - h2.qty), 'POS'
               order by unit;

Oracle version is 11.2.0.2.0

Basically if I can use the aggregated (Qty) for the same Unit, Group, Loc and Startdate value for all Type 2s, and then compare that Agregated Qty of Type 2s with the qty of type 1s by matching up this Unit, Group, Loc and Startdate of all Type 1's and Type 2s, then that is what should return the correct results.

Results expected -

'a',' ','b',9-26-2010,2, -1

-1 is derived from (10 - 11)
Value 2 after the 9-26-2010 date in results above is not coming from my input tables but is manually entered in my select query.

Now, remember, it may or may not be all Type 2s exist in duplicates. Some Type 2s may, others may be not!!

Any help is greatly appreciated.

Thank You!!
Sanders

Edited by: Sanders_2503 on Sep 26, 2012 2:05 PM

Edited by: Sanders_2503 on Sep 26, 2012 2:09 PM

Edited by: Sanders_2503 on Sep 26, 2012 2:32 PM