anumoses
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
--------------------------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.
ASKER
sample enclosed
sample-data.txt
sample-data.txt
expected results ?
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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 :)
ASKER
Thanks. Preparing the sample data and question
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