Oracle Database
Oracle Database

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.

LVL 6

anumosesAsked: 2015-04-10

Who is Participating?

LVL 79

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

>>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

LVL 79

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

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