We help IT Professionals succeed at work.

oracle query

anumoses
anumoses asked
on 
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


data
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.
Comment
Watch Question

View Solution Only

slightwv (䄆 Netminder)
Most Valuable Expert 2012
Distinguished Expert 2019

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

Try this:
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

Open in new window

anumoses

Author

Commented:
Are we missing end case
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
>>Are we missing end case

Yes.

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

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

Open in new window

anumoses

Author

Commented:
perfect
anumoses

Author

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