Oracle query ( using sqlplus)

http://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.
LVL 6
anumosesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

slightwv (䄆 Netminder) Commented:
I believe this is the same basic date manipulation that you used in the last question.

The only difference I see is instead of converting the dates to the Month, you now need the Week and Day of week.

Check the online docs for the different ways to turn a date into the different pieces:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements004.htm#SQLRF00211


Current week number of the year and Day of the week:
select to_char(sysdate,'IW'), to_char(sysdate,'Day') from dual;

Once you figure out what 'columns' you want to SUM, just extend the SQL from the previous query.
0
anumosesAuthor Commented:
select to_char(sysdate,'IW'),

This is week of the year I believe. If the user puts date range of 01-jan-2015 to 31-mar-2015, then we get data for 12 weeks.

Jan                                                                         Feb
week 1  week 2    week 3   week 4        week 1  week 2    week 3   week 4  

Using the week of the year concept will I get the data?
0
slightwv (䄆 Netminder) Commented:
Check the docs.  You can turn a date into just about anything you want:

Month and Week of Month:
select to_char(sysdate,'Mon'), to_char(sysdate,'W') from dual;
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

slightwv (䄆 Netminder) Commented:
Putting it all into a single string:
select to_char(sysdate,'Mon') || ' week' || to_char(sysdate,'W') from dual;
0
anumosesAuthor Commented:
I got the concept. If the user is using a start_date and end_date parameters of 01-jan-2015 and 31-mar-2015..

I tried this query

Select distinct ih.customer_id,patient_name,
sum(case when to_char(ih.invoice_date,'Day') = to_char(:end_date,'Day') then id.order_qty else 0 end) weeks
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

I get total weeks, but I need to get Mon, Tue, Wed, Thu, Fri, Sat, Sun

I get the concept of Week1 and week2 that I need to put as headers. But below week1 I need mon to sun for the date range.
0
slightwv (䄆 Netminder) Commented:
>>sum(case when to_char(ih.invoice_date,'Day') = to_char(:end_date,'Day') then id.order_qty else 0 end)

I don't think that will work.  There will be multiple 'Mondays' in that date range so it will sum them all up.  You'll need a combination of week number and day of week.

>> that I need to put as headers

Using sqlplus column headers you can create a multi-line header.  Therefore you set the header for "Wed" to:
"Week1|Wed"

To see this in action:
col dummy head "Week1|Wed"
select * from dual;


Using the sqlplus trick from your previous question to get the column headings the way you want them, you should be good to go.
0
anumosesAuthor Commented:
Perfect. I wanted to do the report in sqlplus. I will get all column headers. But now distributing the data based on start date and end date is a problem for me. Earlier we used months and now we are using weeks. 12 weeks data.

From the previous query, we are breaking the curr month to 4 weeks data.

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,

to

sum(case when to_char(ih.invoice_date,'Dy') = 'Mon' then ...
This is where I am stuck to proceed further.
0
slightwv (䄆 Netminder) Commented:
>>sum(case when to_char(ih.invoice_date,'Dy') = 'Mon'

Again, this will not work.

For example, using the dates in your original query:
to_date('01-JAN-2015','DD-MON-YYYY')  and to_date('31-MAR-2015','DD-MON-YYYY')

Jan 5th, 12th, 19th, 26, Feb 2nd, etc... are all Mondays and will all be summed up together.

You need to group based on something in addition to the "Day".  You will likely need to add one of the Week values to the SUM.

The different options for 'Week' are in the doc link I posted above.  You will likely use 'IW'. since the Week number will also repeat over the months (there is a week1 in every month...).
0
anumosesAuthor Commented:
I dont think I am able to join different options to get week 1 and days in that for the invoice date. I tried different options but not successful.
0
anumosesAuthor Commented:
Select distinct ih.customer_id,patient_name,
       case when to_char(ih.invoice_date,'Dy') = 'Sun' then to_char(ih.invoice_date,'Mon') || ' week' || to_char(ih.invoice_date,'W') else '0' end as Sun,
	   case when to_char(ih.invoice_date,'Dy') = 'Mon' then to_char(ih.invoice_date,'Mon') || ' week' || to_char(ih.invoice_date,'W') else '0' end as Mon,
	   case when to_char(ih.invoice_date,'Dy') = 'Tue' then to_char(ih.invoice_date,'Mon') || ' week' || to_char(ih.invoice_date,'W') else '0' end as Tue,
	   case when to_char(ih.invoice_date,'Dy') = 'Wed' then to_char(ih.invoice_date,'Mon') || ' week' || to_char(ih.invoice_date,'W') else '0' end as Wed,
	   case when to_char(ih.invoice_date,'Dy') = 'Thu' then to_char(ih.invoice_date,'Mon') || ' week' || to_char(ih.invoice_date,'W') else '0' end as Thu,
	   case when to_char(ih.invoice_date,'Dy') = 'Fri' then to_char(ih.invoice_date,'Mon') || ' week' || to_char(ih.invoice_date,'W') else '0' end as Fri,
	   case when to_char(ih.invoice_date,'Dy') = 'Sat' then to_char(ih.invoice_date,'Mon') || ' week' || to_char(ih.invoice_date,'W') else '0' end as Sat,
		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
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  '01-jan-2015' and '03-jan-2015'
and vp.inv_product_type in ('RBC','LRBC','LPHER','PHER','FFP','FP24','CRYO')
group by ih.customer_id,patient_name,ih.invoice_date

Open in new window


I made this query for Jan 2015 1st week. Now I need help for the data that can go there.

Thurs - 9
Fri - 7
Sat 11
0
slightwv (䄆 Netminder) Commented:
I've been playing with this and think I'm close to something to base your final results on.

There is a problem though:
You say you want 12 weeks.  This implies there are only 4 weeks in a month.

Many times the month starts in the middle of one week and ends in another.  From a data aspect this is technically 5 weeks.

How do you want to handle that?

Take January 2015.

Week1:  would be the 1st to the 3rd
Week 2: 4th - 10th
...
Week 5: 25th - 31st

So a 3 month report from 1/1/2015 to 3/31/2015 should contain 14 weeks.

Remember you need to know the maximum number of output columns before you parse the SQL.
0
anumosesAuthor Commented:
I meant to say 12 weeks in general. based on the start date and end date we have to get the weeks. Your thoughts are correct. Jan 2015 we have 5 weeks.
0
slightwv (䄆 Netminder) Commented:
So which 12 weeks do you want?

The first 12 from the start date or the last 12 backwards from the end date?

Using your sample dates:  to_date('01-JAN-2015','DD-MON-YYYY')  and to_date('31-MAR-2015','DD-MON-YYYY')


Do you want from
Jan 1st  through Mar 21st
or
Jan 11th through Mar 31st
0
anumosesAuthor Commented:
Jan 1st  through Mar 21st
0
slightwv (䄆 Netminder) Commented:
This is pretty ugly but I think it works.

I've asked another Expert to see if he can come up with cleaner SQL using analytics.  I'm sure it can be done but my analytics SQL is pretty weak.

Here is what I have:
--setup the end_date
var start_date varchar2(10)
var end_date varchar2(10)

exec :start_date := '04/01/2015';
exec :end_date := '06/30/2015';

/*
drop table tab1 purge;
create table tab1(mydate date, some_number number);

insert into tab1 values(to_date('04/01/2015','MM/DD/YYYY'),1);
insert into tab1 values(to_date('04/01/2015','MM/DD/YYYY'),2);
insert into tab1 values(to_date('04/02/2015','MM/DD/YYYY'),3);
insert into tab1 values(to_date('04/05/2015','MM/DD/YYYY'),4);
insert into tab1 values(to_date('04/12/2015','MM/DD/YYYY'),5);

insert into tab1 values(to_date('05/02/2015','MM/DD/YYYY'),6);
commit;
*/

--the guts of it
clear columns

col day1 head 'Sun'
col day2 head 'Mon'
col day3 head 'Tue'

col week1_day4 head 'Week 1|Wed'
col week2_day4 head 'Week 2|Wed'
col week3_day4 head 'Week 3|Wed'
col week4_day4 head 'Week 4|Wed'

col day5 head 'Thu'
col day6 head 'Fri'
col day7 head 'Sat'


select  
	sum(case when week_no = 1 and week_day='Sun' then mytotal else 0 end) day1,
	sum(case when week_no = 1 and week_day='Mon' then mytotal else 0 end) day2,
	sum(case when week_no = 1 and week_day='Tue' then mytotal else 0 end) day3,
	sum(case when week_no = 1 and week_day='Wed' then mytotal else 0 end) week1_day4,
	sum(case when week_no = 1 and week_day='Thu' then mytotal else 0 end) day5,
	sum(case when week_no = 1 and week_day='Fri' then mytotal else 0 end) day6,
	sum(case when week_no = 1 and week_day='Sat' then mytotal else 0 end) day7,
	--
	sum(case when week_no = 2 and week_day='Sun' then mytotal else 0 end) day1,
	sum(case when week_no = 2 and week_day='Mon' then mytotal else 0 end) day2,
	sum(case when week_no = 2 and week_day='Tue' then mytotal else 0 end) day3,
	sum(case when week_no = 2 and week_day='Wed' then mytotal else 0 end) week2_day4,
	sum(case when week_no = 2 and week_day='Thu' then mytotal else 0 end) day5,
	sum(case when week_no = 2 and week_day='Fri' then mytotal else 0 end) day6,
	sum(case when week_no = 2 and week_day='Sat' then mytotal else 0 end) day7,
	--
	sum(case when week_no = 3 and week_day='Sun' then mytotal else 0 end) day1,
	sum(case when week_no = 3 and week_day='Mon' then mytotal else 0 end) day2,
	sum(case when week_no = 3 and week_day='Tue' then mytotal else 0 end) day3,
	sum(case when week_no = 3 and week_day='Wed' then mytotal else 0 end) week3_day4,
	sum(case when week_no = 3 and week_day='Thu' then mytotal else 0 end) day5,
	sum(case when week_no = 3 and week_day='Fri' then mytotal else 0 end) day6,
	sum(case when week_no = 3 and week_day='Sat' then mytotal else 0 end) day7,
	--
	sum(case when week_no = 4 and week_day='Sun' then mytotal else 0 end) day1,
	sum(case when week_no = 4 and week_day='Mon' then mytotal else 0 end) day2,
	sum(case when week_no = 4 and week_day='Tue' then mytotal else 0 end) day3,
	sum(case when week_no = 4 and week_day='Wed' then mytotal else 0 end) week4_day4,
	sum(case when week_no = 4 and week_day='Thu' then mytotal else 0 end) day5,
	sum(case when week_no = 4 and week_day='Fri' then mytotal else 0 end) day6,
	sum(case when week_no = 4 and week_day='Sat' then mytotal else 0 end) day7
from (
	select 
		( to_number(to_char(mydate,'IW')) - to_number(to_char(to_date(:start_date,'MM/DD/YYYY'),'IW')))+1 week_no,
		to_char(mydate,'Dy') week_day
		,sum(some_number) mytotal
	from tab1
	group by
		( to_number(to_char(mydate,'IW')) - to_number(to_char(to_date(:start_date,'MM/DD/YYYY'),'IW')))+1,
		to_char(mydate,'Dy')
	order by 1,2
)
/

Open in new window


My results:
                                     Week 1
       Sun        Mon        Tue        Wed        Thu        Fri        Sat
---------- ---------- ---------- ---------- ---------- ---------- ----------
                                     Week 2
       Sun        Mon        Tue        Wed        Thu        Fri        Sat
---------- ---------- ---------- ---------- ---------- ---------- ----------
                                     Week 3
       Sun        Mon        Tue        Wed        Thu        Fri        Sat
---------- ---------- ---------- ---------- ---------- ---------- ----------
                                     Week 4
       Sun        Mon        Tue        Wed        Thu        Fri        Sat
---------- ---------- ---------- ---------- ---------- ---------- ----------
         4          0          0          3          3          0          0
         5          0          0          0          0          0          0
         0          0          0          0          0          0          0
         0          0          0          0          0          0          0

Open in new window

0

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
slightwv (䄆 Netminder) Commented:
I should clarify my results.  If you set linesize in sqplus correctly everything should appear on one line.

I didn't set it when I ran the test so it wrapped the data onto multiple lines.
0
anumosesAuthor Commented:
As you mentioned earlier, do I have to wait for any other expert suggestions?
0
sdstuberCommented:
the inner SUM aggregation and ordering aren't helpful to the outer select, so you can simply remove them


SELECT SUM(CASE WHEN week_no = 1 AND week_day = 'Sun' THEN some_number ELSE 0 END) day1,
       SUM(CASE WHEN week_no = 1 AND week_day = 'Mon' THEN some_number ELSE 0 END) day2,
       SUM(CASE WHEN week_no = 1 AND week_day = 'Tue' THEN some_number ELSE 0 END) day3,
       SUM(CASE WHEN week_no = 1 AND week_day = 'Wed' THEN some_number ELSE 0 END) week1_day4,
       SUM(CASE WHEN week_no = 1 AND week_day = 'Thu' THEN some_number ELSE 0 END) day5,
       SUM(CASE WHEN week_no = 1 AND week_day = 'Fri' THEN some_number ELSE 0 END) day6,
       SUM(CASE WHEN week_no = 1 AND week_day = 'Sat' THEN some_number ELSE 0 END) day7,
       --
       SUM(CASE WHEN week_no = 2 AND week_day = 'Sun' THEN some_number ELSE 0 END) day1,
       SUM(CASE WHEN week_no = 2 AND week_day = 'Mon' THEN some_number ELSE 0 END) day2,
       SUM(CASE WHEN week_no = 2 AND week_day = 'Tue' THEN some_number ELSE 0 END) day3,
       SUM(CASE WHEN week_no = 2 AND week_day = 'Wed' THEN some_number ELSE 0 END) week2_day4,
       SUM(CASE WHEN week_no = 2 AND week_day = 'Thu' THEN some_number ELSE 0 END) day5,
       SUM(CASE WHEN week_no = 2 AND week_day = 'Fri' THEN some_number ELSE 0 END) day6,
       SUM(CASE WHEN week_no = 2 AND week_day = 'Sat' THEN some_number ELSE 0 END) day7,
       --
       SUM(CASE WHEN week_no = 3 AND week_day = 'Sun' THEN some_number ELSE 0 END) day1,
       SUM(CASE WHEN week_no = 3 AND week_day = 'Mon' THEN some_number ELSE 0 END) day2,
       SUM(CASE WHEN week_no = 3 AND week_day = 'Tue' THEN some_number ELSE 0 END) day3,
       SUM(CASE WHEN week_no = 3 AND week_day = 'Wed' THEN some_number ELSE 0 END) week3_day4,
       SUM(CASE WHEN week_no = 3 AND week_day = 'Thu' THEN some_number ELSE 0 END) day5,
       SUM(CASE WHEN week_no = 3 AND week_day = 'Fri' THEN some_number ELSE 0 END) day6,
       SUM(CASE WHEN week_no = 3 AND week_day = 'Sat' THEN some_number ELSE 0 END) day7,
       --
       SUM(CASE WHEN week_no = 4 AND week_day = 'Sun' THEN some_number ELSE 0 END) day1,
       SUM(CASE WHEN week_no = 4 AND week_day = 'Mon' THEN some_number ELSE 0 END) day2,
       SUM(CASE WHEN week_no = 4 AND week_day = 'Tue' THEN some_number ELSE 0 END) day3,
       SUM(CASE WHEN week_no = 4 AND week_day = 'Wed' THEN some_number ELSE 0 END) week4_day4,
       SUM(CASE WHEN week_no = 4 AND week_day = 'Thu' THEN some_number ELSE 0 END) day5,
       SUM(CASE WHEN week_no = 4 AND week_day = 'Fri' THEN some_number ELSE 0 END) day6,
       SUM(CASE WHEN week_no = 4 AND week_day = 'Sat' THEN some_number ELSE 0 END) day7
  FROM (SELECT   (  TO_NUMBER(TO_CHAR(mydate, 'IW'))
                  - TO_NUMBER(TO_CHAR(TO_DATE( :start_date, 'MM/DD/YYYY'), 'IW')))
               + 1
                   week_no,
               TO_CHAR(mydate, 'Dy') week_day,
               some_number
          FROM tab1);
0
slightwv (䄆 Netminder) Commented:
You can if you wish.  I'm not sure if they will post or not.  They might not have a simpler solution.
0
anumosesAuthor Commented:
what are the thoughts on @sdstuber comments?
0
slightwv (䄆 Netminder) Commented:
>>what are the thoughts on @sdstuber comments?

Considering he is also one of the top Oracle guys on the site, why would I need to comment?

He is correct.  I had the inner sum and order when I was experimenting.  I just never removed them.
0
anumosesAuthor Commented:
I know you both now for a very long time and you both have helped me in most of my queries. I always trust on the answers I from both of you. Thanks
0
sdstuberCommented:
The query is just a basic pivot.  The only complication is the pivot criteria of 2 values (week and day) rather than a single value.

As for my previous comments,  just think about the math for a minute....

sum(sum(x)) = sum(x)    

so, summing twice doesn't help.

Using the order by doesn't help because the order of the data is negated by aggregating into a single row, plus, nothing else in the outer query ever references the ordering so even if it did have an impact it's reliably usable in the current form anyway.


and of course, you can always just run it and see it returns the same results as the previous answer
0
anumosesAuthor Commented:
ORA-01858: a non-numeric character was found where a numeric was expected
0
slightwv (䄆 Netminder) Commented:
>>ORA-01858: a non-numeric character was found where a numeric was expected

Both queries run for me using the test case I posted.

Check the date string versus the TO_DATE format mask.

The code I posted uses MM/DD/YYYY


This is why we keep stressing to you the importance of explicit data type conversions.

You really need to physically provide the format mask when converting strings to dates.
0
anumosesAuthor Commented:
Yes you are correct. Now I replace

mydate with my invoice_date
 and
 some_number  with order_qty.
0
slightwv (䄆 Netminder) Commented:
>>mydate with my invoice_date   and    some_number  with order_qty.

Sounds correct but only you know your tables and data.
0
anumosesAuthor Commented:
I just had one question. If I  have to add end_date parameter in the query how would I accomplish it?
0
slightwv (䄆 Netminder) Commented:
>>If I  have to add end_date parameter in the query how would I accomplish it?

Same as before?

The main pieces of the code don't use END_DATE so you only need it to limit the rows being returned in the inner query.
0
anumosesAuthor Commented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.