We help IT Professionals succeed at work.
Get Started

Oracle query ( using sqlplus)

anumoses
anumoses asked
on
153 Views
Last Modified: 2015-04-21
https://www.experts-exchange.com/Database/Oracle/Q_28654739.html

Extension to the above answered question.

Using SQLPLUS since reports designer will not allow design for 12 weeks.

The user needs by weeks now. 12 week period.

var end_date varchar2(20);
exec :end_date := '31-MAR-2015';

Select distinct ih.customer_id,patient_name,
		sum(case when to_char(ih.invoice_date,'Mon')=to_char(to_date(:end_date,'DD-MON-YYYY'),'Mon') then id.order_qty else 0 end) curr_month,
		sum(case when to_char(ih.invoice_date,'Mon')=to_char(add_months(to_date(:end_date,'DD-MON-YYYY'),-1),'Mon') then id.order_qty else 0 end) last_month,
		sum(case when to_char(ih.invoice_date,'Mon')=to_char(add_months(to_date(:end_date,'DD-MON-YYYY'),-2),'Mon') then id.order_qty else 0 end) prev_month
from       tab1 ih,
           tab2 id,
           tab4 ip,
           tab3 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 ih.customer_id = 'WAD-EX0128'
and   ih.invoice_date between  to_date('01-JAN-2015','DD-MON-YYYY')  and to_date('31-MAR-2015','DD-MON-YYYY')
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

                                                                                week 1                                               Week 2
customer id     name   patient_name    Mon  Tue   Wed  Thu  Fri   Sat    Sun         Mon  Tue    Wed  Thu  Fri   Sat  Sun

so on till week 12. If I get the concept of doing it for 1 week, then I will do the same for rest of the weeks...

3 months concept but data distributed by weeks.  Help is appreciated.
Comment
Watch Question
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2020
Commented:
This problem has been solved!
Unlock 1 Answer and 30 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE