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


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.
LVL 6
anumosesAsked:
Who is Participating?
 
slightwv (䄆 Netminder) 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

0
 
slightwv (䄆 Netminder) 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

0
 
anumosesAuthor Commented:
Are we missing end case
0
 
anumosesAuthor Commented:
perfect
0
 
anumosesAuthor Commented:
Thanks. I have another question regarding the same query of using parameters for the months instead of hardcoding dates.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.