oracle query

Select distinct ih.customer_id,patient_name,
           sum(id.order_qty) qty           
from       invoice_header ih,
           invoice_detail id,
           item_profile ip,
           valid_products@plab.world vp
where id.invoice_number = ih.invoice_number
and id.item_id = vp.product_code
and   id.item_id = ip.item_id
and   ip.item_type in ('P')
and customer_id = 'WAD-EX0128'
and   ih.invoice_date between  '01-JAN-2015' and '31-JAN-2015'
and vp.inv_product_type in ('RBC','LRBC','LPHER','PHER','FFP','FP24','CRYO')
group by ih.customer_id,patient_name
union all
Select distinct ih.customer_id,patient_name,
           sum(id.order_qty) qty           
from       invoice_header ih,
           invoice_detail id,
           item_profile ip,
           valid_products@plab.world vp
where id.invoice_number = ih.invoice_number
and id.item_id = vp.product_code
and   id.item_id = ip.item_id
and   ip.item_type in ('P')
and customer_id = 'WAD-EX0128'
and   ih.invoice_date between  '01-FEB-2015' and '28-FEB-2015'
and vp.inv_product_type in ('RBC','LRBC','LPHER','PHER','FFP','FP24','CRYO')
group by ih.customer_id,patient_name
union all
Select distinct ih.customer_id,patient_name,
           sum(id.order_qty) qty           
from       invoice_header ih,
           invoice_detail id,
           item_profile ip,
           valid_products@plab.world vp
where id.invoice_number = ih.invoice_number
and id.item_id = vp.product_code
and   id.item_id = ip.item_id
and   ip.item_type in ('P')
and customer_id = 'WAD-EX0128'
and   ih.invoice_date between  '01-MAR-2015' and '31-MAR-2015'
and vp.inv_product_type in ('RBC','LRBC','LPHER','PHER','FFP','FP24','CRYO')
group by ih.customer_id,patient_name

Open in new window

Required

Customer id Patient name JAN FEB MAR
WAD-EX0128 ALEXIAN BROTHERS MEDICAL CENTER 515 442 510

Help appreciated. I tried case but did not get the correct query.

Select distinct ih.customer_id,patient_name, sum(case when to_char(ih.invoice_date,'Mon') = 'Jan' then id.order_qty else 0) Jan, sum(case when to_char(ih.invoice_date,'Mon') = 'Feb' then id.order_qty else 0) Feb, sum(case when to_char(ih.invoice_date,'Mon') = 'Mar' then id.order_qty else 0) Mar from invoice_header ih, invoice_detail id, item_profile ip, valid_products@plab.world vp where id.invoice_number = ih.invoice_number and id.item_id = vp.product_code and id.item_id = ip.item_id and ip.item_type in ('P') and customer_id = 'WAD-EX0128' and ih.invoice_date between '01-JAN-2015' and '31-MAR-2015' and vp.inv_product_type in ('RBC','LRBC','LPHER','PHER','FFP','FP24','CRYO') group by ih.customer_id,patient_name

Select distinct ih.customer_id,patient_name, sum(case when to_char(ih.invoice_date,'Mon') = 'Jan' then id.order_qty else 0 end) Jan, sum(case when to_char(ih.invoice_date,'Mon') = 'Feb' then id.order_qty else 0 end) Feb, sum(case when to_char(ih.invoice_date,'Mon') = 'Mar' then id.order_qty else 0 end) Mar from invoice_header ih, invoice_detail id, item_profile ip, valid_products@plab.world vp where id.invoice_number = ih.invoice_number and id.item_id = vp.product_code and id.item_id = ip.item_id and ip.item_type in ('P') and customer_id = 'WAD-EX0128' and ih.invoice_date between '01-JAN-2015' and '31-MAR-2015' and vp.inv_product_type in ('RBC','LRBC','LPHER','PHER','FFP','FP24','CRYO') group by ih.customer_id,patient_name

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 80

slightwv (䄆 Netminder) Commented: 2015-04-10

I don't think you need the UNION's.

Try this:

LVL 6

anumosesAuthor Commented: 2015-04-10

Are we missing end case

>>Are we missing end case

Yes.

I just typed it in. Can't test it since I don't have your tables.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

perfect

anumosesAuthor Commented: 2015-04-13

Thanks. I have another question regarding the same query of using parameters for the months instead of hardcoding dates.

oracle query

Who is Participating?