Link to home
Start Free TrialLog in
Avatar of anumoses
anumosesFlag for United States of America

asked on

oracle query from union

select area_rep_no,
       to_char(drive_date,'MON') as Month,
       DRIVE_DATE,
       sum(projection) as projection ,
	   insert_date,
	   'New Drive/Projection Added' as reason
  from blood_drives
 where insert_date between '04-aug-2014' and '10-aug-2014'
   and drive_cancelled is null
   and area_rep_no =6 
   and drive_date between '01-aug-2014' and '31-dec-2014'
   group by area_rep_NO,drive_date,insert_date
union     
select b.area_rep_no,
       to_char(b.drive_date,'MON') as Month,
       DRIVE_DATE,--to_date(to_char(drive_date,'MON'),'MON') as drive_date,
	   sum(b.projection) as projection,
       audit_insert_date as insert_date,
       column_name
  from blood_drives_audit a,blood_drives b 
 where a.audit_key = b.audit_key
  and area_rep_no =6 
  and column_name in ('drive_id' ,'drive_cancelled','projection' )
  and audit_insert_date between '04-aug-2014' and '10-aug-2014'  
  group by area_rep_no,
  DRIVE_DATE,to_char(drive_date,'MON'),
  audit_insert_date, 
  column_name
  order by area_rep_no,drive_date 

Open in new window

-----------------------------


REP,   MONTH,   DRIVE_DATE,   PROJECTION,   INSERT_DATE,   REASON
6,         SEP,        9/4/2014,          20,                  8/7/2014,    New Drive/Projection Added
6,         SEP,        9/18/2014,       17,                  8/6/2014,     drive_cancelled
6,        OCT,      10/30/2014,       23,                  8/7/2014,     drive_id

My requirement is if column_name from blood_drives_audit table = 'drive_cancelled' then if any projection exists for the same month in blood_drives table, then we have to subtract. eg in the above case 20 is added in blood_drives
and 17 is drive cancelled. I don't have to show rep,drive date or insert date but added to show an example. I tried doing a case statement, but was not exactly coming right. Need help. If any sample table or data required let me know. I have to create that and will send.
Avatar of Sean Stuber
Sean Stuber

what are you trying to show above:  input data, current output data, expected output data,  something else?

please post input data and expected output data

we do NOT need current output, given input data, I can run the query and generate that myself
Avatar of anumoses

ASKER

sample enclosed
sample-data.txt
expected results ?
REP,   MONTH,   DRIVE_DATE,   PROJECTION,   INSERT_DATE,   REASON
6,      SEP,           9/4/2014,         3,                 8/7/2014,      New Drive/Projection Added
6,     OCT,        10/30/2014,      25,                 8/7/2014,     drive_id


As i mentioned no need to display insert_date drive_date,reason
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
If you see my original query reason is a pseudo column. I named it as reason.
second query after union has the column name as column_name.
 
'New Drive/Projection Added' as reason

When I try to run my original query with the solution provided, I am getting errors as reason column does not exist.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Perfect solution. There is another situation that I need help in the same query. I will post another question. Hope you will help me.
Thanks anumoses. Some one will undoubtedly look after the next question - perhaps me :)
Thanks. Preparing the sample data and question