optimizing oracle query

I know many experts have helped me getting to this point. I am posting a query with sample data. Can someone help me to optimize it? Its taking time to execute.

select ym,customer_id,patient_name,

       nullif(substr(month_string,1,instr(month_string,',') - 1),'~') sun_1,

       nullif(substr(month_string,instr(month_string,',',1,1) + 1,instr(month_string,',',1,2) - instr(month_string,',',1,1) - 1),'~') mon_1,

       nullif(substr(month_string,instr(month_string,',',1,2) + 1,instr(month_string,',',1,3) - instr(month_string,',',1,2) - 1),'~') tue_1,

       nullif(substr(month_string,instr(month_string,',',1,3) + 1,instr(month_string,',',1,4) - instr(month_string,',',1,3) - 1),'~') wed_1,

       nullif(substr(month_string,instr(month_string,',',1,4) + 1,instr(month_string,',',1,5) - instr(month_string,',',1,4) - 1),'~') thu_1,

       nullif(substr(month_string,instr(month_string,',',1,5) + 1,instr(month_string,',',1,6) - instr(month_string,',',1,5) - 1),'~') fri_1,

       nullif(substr(month_string,instr(month_string,',',1,6) + 1,instr(month_string,',',1,7) - instr(month_string,',',1,6) - 1),'~') sat_1,

       substr(month_string,instr(month_string,',',1,7) + 1,instr(month_string,',',1,8) - instr(month_string,',',1,7) - 1) sun_2,

       substr(month_string,instr(month_string,',',1,8) + 1,instr(month_string,',',1,9) - instr(month_string,',',1,8) - 1) mon_2,

       substr(month_string,instr(month_string,',',1,9) + 1,instr(month_string,',',1,10) - instr(month_string,',',1,9) - 1) tue_2,

       substr(month_string,instr(month_string,',',1,10) + 1,instr(month_string,',',1,11) - instr(month_string,',',1,10) - 1) wed_2,

       substr(month_string,instr(month_string,',',1,11) + 1,instr(month_string,',',1,12) - instr(month_string,',',1,11) - 1) thu_2,

       substr(month_string,instr(month_string,',',1,12) + 1,instr(month_string,',',1,13) - instr(month_string,',',1,12) - 1) fri_2,

       substr(month_string,instr(month_string,',',1,13) + 1,instr(month_string,',',1,14) - instr(month_string,',',1,13) - 1) sat_2,

       substr(month_string,instr(month_string,',',1,14) + 1,instr(month_string,',',1,15) - instr(month_string,',',1,14) - 1) sun_3,

       substr(month_string,instr(month_string,',',1,15) + 1,instr(month_string,',',1,16) - instr(month_string,',',1,15) - 1) mon_3,

       substr(month_string,instr(month_string,',',1,16) + 1,instr(month_string,',',1,17) - instr(month_string,',',1,16) - 1) tue_3,

       substr(month_string,instr(month_string,',',1,17) + 1,instr(month_string,',',1,18) - instr(month_string,',',1,17) - 1) wed_3,

       substr(month_string,instr(month_string,',',1,18) + 1,instr(month_string,',',1,19) - instr(month_string,',',1,18) - 1) thu_3,

       substr(month_string,instr(month_string,',',1,19) + 1,instr(month_string,',',1,20) - instr(month_string,',',1,19) - 1) fri_3,

       substr(month_string,instr(month_string,',',1,20) + 1,instr(month_string,',',1,21) - instr(month_string,',',1,20) - 1) sat_3,

       substr(month_string,instr(month_string,',',1,21) + 1,instr(month_string,',',1,22) - instr(month_string,',',1,21) - 1) sun_4,

       substr(month_string,instr(month_string,',',1,22) + 1,instr(month_string,',',1,23) - instr(month_string,',',1,22) - 1) mon_4,

       substr(month_string,instr(month_string,',',1,23) + 1,instr(month_string,',',1,24) - instr(month_string,',',1,23) - 1) tue_4,

       substr(month_string,instr(month_string,',',1,24) + 1,instr(month_string,',',1,25) - instr(month_string,',',1,24) - 1) wed_4,

       substr(month_string,instr(month_string,',',1,25) + 1,instr(month_string,',',1,26) - instr(month_string,',',1,25) - 1) thu_4,

       substr(month_string,instr(month_string,',',1,26) + 1,instr(month_string,',',1,27) - instr(month_string,',',1,26) - 1) fri_4,

       substr(month_string,instr(month_string,',',1,27) + 1,instr(month_string,',',1,28) - instr(month_string,',',1,27) - 1) sat_4,

       nullif(substr(month_string,instr(month_string,',',1,28) + 1,instr(month_string,',',1,29) - instr(month_string,',',1,28) - 1),'~') sun_5,

       nullif(substr(month_string,instr(month_string,',',1,29) + 1,instr(month_string,',',1,30) - instr(month_string,',',1,29) - 1),'~') mon_5,

       nullif(substr(month_string,instr(month_string,',',1,30) + 1,instr(month_string,',',1,31) - instr(month_string,',',1,30) - 1),'~') tue_5,

       nullif(substr(month_string,instr(month_string,',',1,31) + 1,instr(month_string,',',1,32) - instr(month_string,',',1,31) - 1),'~') wed_5,

       nullif(substr(month_string,instr(month_string,',',1,32) + 1,instr(month_string,',',1,33) - instr(month_string,',',1,32) - 1),'~') thu_5,

       nullif(substr(month_string,instr(month_string,',',1,33) + 1,instr(month_string,',',1,34) - instr(month_string,',',1,33) - 1),'~') fri_5,

       nullif(substr(month_string,instr(month_string,',',1,34) + 1,instr(month_string,',',1,35) - instr(month_string,',',1,34) - 1),'~') sat_5,

       nullif(substr(month_string,instr(month_string,',',1,35) + 1,instr(month_string,',',1,36) - instr(month_string,',',1,35) - 1),'~') sun_6,

       nullif(substr(month_string,instr(month_string,',',1,36) + 1,instr(month_string,',',1,37) - instr(month_string,',',1,36) - 1),'~') mon_6,

       nullif(substr(month_string,instr(month_string,',',1,37) + 1,instr(month_string,',',1,38) - instr(month_string,',',1,37) - 1),'~') tue_6

  from (select ym,customer_id,patient_name,

               case when to_date(ym,'yyyymm') - trunc(to_date(ym,'yyyymm'),'iw') + 1 < 7

                    then rpad('~,',2 * (to_date(ym,'yyyymm') - trunc(to_date(ym,'yyyymm'),'iw') + 1),'~,')

               end||

               days_in_month||

               rpad('~,',

                    2 * (length(replace(case when to_date(ym,'yyyymm') - trunc(to_date(ym,'yyyymm'),'iw') + 1 < 7

                                             then rpad('~,',2 * (to_date(ym,'yyyymm') - trunc(to_date(ym,'yyyymm'),'iw') + 1),'~,')

                                        end||days_in_month,

                                        ','

                                       )

                               ) -

                         length(case when to_date(ym,'yyyymm') - trunc(to_date(ym,'yyyymm'),'iw') + 1 < 7

                                     then rpad('~,',2 * (to_date(ym,'yyyymm') - trunc(to_date(ym,'yyyymm'),'iw') + 1),'~,')

                                end||days_in_month

                               ) +

                         38

                   ),

                   '~,'

                   ) month_string

          from (select to_char(trunc(ih.invoice_date,'mm'),'yyyymm') ym,

                       ih.customer_id,

                       patient_name,

                       to_char(count(case when to_char(ih.invoice_date,'dd') = '01' then 1 end))||','||

                       to_char(count(case when to_char(ih.invoice_date,'dd') = '02' then 1 end))||','||

                       to_char(count(case when to_char(ih.invoice_date,'dd') = '03' then 1 end))||','||

                       to_char(count(case when to_char(ih.invoice_date,'dd') = '04' then 1 end))||','||

                       to_char(count(case when to_char(ih.invoice_date,'dd') = '05' then 1 end))||','||

                       to_char(count(case when to_char(ih.invoice_date,'dd') = '06' then 1 end))||','||

                       to_char(count(case when to_char(ih.invoice_date,'dd') = '07' then 1 end))||','||

                       to_char(count(case when to_char(ih.invoice_date,'dd') = '08' then 1 end))||','||

                       to_char(count(case when to_char(ih.invoice_date,'dd') = '09' then 1 end))||','||

                       to_char(count(case when to_char(ih.invoice_date,'dd') = '10' then 1 end))||','||

                       to_char(count(case when to_char(ih.invoice_date,'dd') = '11' then 1 end))||','||

                       to_char(count(case when to_char(ih.invoice_date,'dd') = '12' then 1 end))||','||

                       to_char(count(case when to_char(ih.invoice_date,'dd') = '13' then 1 end))||','||

                       to_char(count(case when to_char(ih.invoice_date,'dd') = '14' then 1 end))||','||

                       to_char(count(case when to_char(ih.invoice_date,'dd') = '15' then 1 end))||','||

                       to_char(count(case when to_char(ih.invoice_date,'dd') = '16' then 1 end))||','||

                       to_char(count(case when to_char(ih.invoice_date,'dd') = '17' then 1 end))||','||

                       to_char(count(case when to_char(ih.invoice_date,'dd') = '18' then 1 end))||','||

                       to_char(count(case when to_char(ih.invoice_date,'dd') = '19' then 1 end))||','||

                       to_char(count(case when to_char(ih.invoice_date,'dd') = '20' then 1 end))||','||

                       to_char(count(case when to_char(ih.invoice_date,'dd') = '21' then 1 end))||','||

                       to_char(count(case when to_char(ih.invoice_date,'dd') = '22' then 1 end))||','||

                       to_char(count(case when to_char(ih.invoice_date,'dd') = '23' then 1 end))||','||

                       to_char(count(case when to_char(ih.invoice_date,'dd') = '24' then 1 end))||','||

                       to_char(count(case when to_char(ih.invoice_date,'dd') = '24' then 1 end))||','||

                       to_char(count(case when to_char(ih.invoice_date,'dd') = '25' then 1 end))||','||

                       to_char(count(case when to_char(ih.invoice_date,'dd') = '27' then 1 end))||','||

                       to_char(count(case when to_char(ih.invoice_date,'dd') = '28' then 1 end))||','||

                       case when to_char(last_day(trunc(ih.invoice_date,'mm')),'dd') >= '29'

                            then to_char(count(case when to_char(ih.invoice_date,'dd') = '29' then 1 end))

                            else '~'

                       end||','||

                       case when to_char(last_day(trunc(ih.invoice_date,'mm')),'dd') >= '30'

                            then to_char(count(case when to_char(ih.invoice_date,'dd') = '30' then 1 end))

                            else '~'

                       end||','||

                       case when to_char(last_day(trunc(ih.invoice_date,'mm')),'dd') >= '31'

                            then to_char(count(case when to_char(ih.invoice_date,'dd') = '31' then 1 end))

                            else '~'

                       end||',' days_in_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(:date_from,'DD-MON-YYYY') and to_date(:date_to,'DD-MON-YYYY')

                   and vp.inv_product_type in ('RBC','LRBC','LPHER','PHER','FFP','FP24','CRYO')

                 group by ih.customer_id,patient_name,trunc(ih.invoice_date,'mm')

               )

       )

order by ym,customer_id,patient_name
							
 

Open in new window

sample-date.txt
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:
'faster' is not a tuning goal.

How long does it take now?
What are you expectations on how long it should take?
What indexes currently exist on the tables?


Post the execution plan from you system.
0
slightwv (䄆 Netminder) Commented:
Also:  PLEASE no more several thousand row samples!!!!!!!!!!!

In the previous question of yours my sample tables only had a few rows.  That should be all you need.
0
sdstuberCommented:
what are the testing values you are using for the date range?

I tried 01-JAN-2015  to 01-JAN-2016 and it finished with all of your data in 0.07 seconds.

I doubt it's going to get much faster than that.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

slightwv (䄆 Netminder) Commented:
Are you monitoring system resources when you run this?
My guess is you are CPU bound.

In my tests, it is a lot slower than sdstuber but my play DB is on a slow VM.  Averaging about 8-12 seconds after flushing caches.

Why are you concatenating the days counts then using substr/instr to pull them back apart?

That is what slows it down on my system.

You are constantly starting over at position 1 for every substr/instr call.  That can take a lot of CPU.
0
sdstuberCommented:
I simplified your query a bit.

All of the "~" operations were unnecessary
If you are cpu bound, then fewer function calls and less concatenation should help

I also removed the extra CASE conditions for 29,30,31 day months.
Just let the NULLS fall through on their own.

  SELECT ym,
         customer_id,
         patient_name,
         SUBSTR(month_string, 1, INSTR(month_string, ',') - 1) sun_1,
         SUBSTR(
             month_string,
               INSTR(
                   month_string,
                   ',',
                   1,
                   1
               )
             + 1,
               INSTR(
                   month_string,
                   ',',
                   1,
                   2
               )
             - INSTR(
                   month_string,
                   ',',
                   1,
                   1
               )
             - 1
         )
             mon_1,
         SUBSTR(
             month_string,
               INSTR(
                   month_string,
                   ',',
                   1,
                   2
               )
             + 1,
               INSTR(
                   month_string,
                   ',',
                   1,
                   3
               )
             - INSTR(
                   month_string,
                   ',',
                   1,
                   2
               )
             - 1
         )
             tue_1,
         SUBSTR(
             month_string,
               INSTR(
                   month_string,
                   ',',
                   1,
                   3
               )
             + 1,
               INSTR(
                   month_string,
                   ',',
                   1,
                   4
               )
             - INSTR(
                   month_string,
                   ',',
                   1,
                   3
               )
             - 1
         )
             wed_1,
         SUBSTR(
             month_string,
               INSTR(
                   month_string,
                   ',',
                   1,
                   4
               )
             + 1,
               INSTR(
                   month_string,
                   ',',
                   1,
                   5
               )
             - INSTR(
                   month_string,
                   ',',
                   1,
                   4
               )
             - 1
         )
             thu_1,
         SUBSTR(
             month_string,
               INSTR(
                   month_string,
                   ',',
                   1,
                   5
               )
             + 1,
               INSTR(
                   month_string,
                   ',',
                   1,
                   6
               )
             - INSTR(
                   month_string,
                   ',',
                   1,
                   5
               )
             - 1
         )
             fri_1,
         SUBSTR(
             month_string,
               INSTR(
                   month_string,
                   ',',
                   1,
                   6
               )
             + 1,
               INSTR(
                   month_string,
                   ',',
                   1,
                   7
               )
             - INSTR(
                   month_string,
                   ',',
                   1,
                   6
               )
             - 1
         )
             sat_1,
         SUBSTR(
             month_string,
               INSTR(
                   month_string,
                   ',',
                   1,
                   7
               )
             + 1,
               INSTR(
                   month_string,
                   ',',
                   1,
                   8
               )
             - INSTR(
                   month_string,
                   ',',
                   1,
                   7
               )
             - 1
         )
             sun_2,
         SUBSTR(
             month_string,
               INSTR(
                   month_string,
                   ',',
                   1,
                   8
               )
             + 1,
               INSTR(
                   month_string,
                   ',',
                   1,
                   9
               )
             - INSTR(
                   month_string,
                   ',',
                   1,
                   8
               )
             - 1
         )
             mon_2,
         SUBSTR(
             month_string,
               INSTR(
                   month_string,
                   ',',
                   1,
                   9
               )
             + 1,
               INSTR(
                   month_string,
                   ',',
                   1,
                   10
               )
             - INSTR(
                   month_string,
                   ',',
                   1,
                   9
               )
             - 1
         )
             tue_2,
         SUBSTR(
             month_string,
               INSTR(
                   month_string,
                   ',',
                   1,
                   10
               )
             + 1,
               INSTR(
                   month_string,
                   ',',
                   1,
                   11
               )
             - INSTR(
                   month_string,
                   ',',
                   1,
                   10
               )
             - 1
         )
             wed_2,
         SUBSTR(
             month_string,
               INSTR(
                   month_string,
                   ',',
                   1,
                   11
               )
             + 1,
               INSTR(
                   month_string,
                   ',',
                   1,
                   12
               )
             - INSTR(
                   month_string,
                   ',',
                   1,
                   11
               )
             - 1
         )
             thu_2,
         SUBSTR(
             month_string,
               INSTR(
                   month_string,
                   ',',
                   1,
                   12
               )
             + 1,
               INSTR(
                   month_string,
                   ',',
                   1,
                   13
               )
             - INSTR(
                   month_string,
                   ',',
                   1,
                   12
               )
             - 1
         )
             fri_2,
         SUBSTR(
             month_string,
               INSTR(
                   month_string,
                   ',',
                   1,
                   13
               )
             + 1,
               INSTR(
                   month_string,
                   ',',
                   1,
                   14
               )
             - INSTR(
                   month_string,
                   ',',
                   1,
                   13
               )
             - 1
         )
             sat_2,
         SUBSTR(
             month_string,
               INSTR(
                   month_string,
                   ',',
                   1,
                   14
               )
             + 1,
               INSTR(
                   month_string,
                   ',',
                   1,
                   15
               )
             - INSTR(
                   month_string,
                   ',',
                   1,
                   14
               )
             - 1
         )
             sun_3,
         SUBSTR(
             month_string,
               INSTR(
                   month_string,
                   ',',
                   1,
                   15
               )
             + 1,
               INSTR(
                   month_string,
                   ',',
                   1,
                   16
               )
             - INSTR(
                   month_string,
                   ',',
                   1,
                   15
               )
             - 1
         )
             mon_3,
         SUBSTR(
             month_string,
               INSTR(
                   month_string,
                   ',',
                   1,
                   16
               )
             + 1,
               INSTR(
                   month_string,
                   ',',
                   1,
                   17
               )
             - INSTR(
                   month_string,
                   ',',
                   1,
                   16
               )
             - 1
         )
             tue_3,
         SUBSTR(
             month_string,
               INSTR(
                   month_string,
                   ',',
                   1,
                   17
               )
             + 1,
               INSTR(
                   month_string,
                   ',',
                   1,
                   18
               )
             - INSTR(
                   month_string,
                   ',',
                   1,
                   17
               )
             - 1
         )
             wed_3,
         SUBSTR(
             month_string,
               INSTR(
                   month_string,
                   ',',
                   1,
                   18
               )
             + 1,
               INSTR(
                   month_string,
                   ',',
                   1,
                   19
               )
             - INSTR(
                   month_string,
                   ',',
                   1,
                   18
               )
             - 1
         )
             thu_3,
         SUBSTR(
             month_string,
               INSTR(
                   month_string,
                   ',',
                   1,
                   19
               )
             + 1,
               INSTR(
                   month_string,
                   ',',
                   1,
                   20
               )
             - INSTR(
                   month_string,
                   ',',
                   1,
                   19
               )
             - 1
         )
             fri_3,
         SUBSTR(
             month_string,
               INSTR(
                   month_string,
                   ',',
                   1,
                   20
               )
             + 1,
               INSTR(
                   month_string,
                   ',',
                   1,
                   21
               )
             - INSTR(
                   month_string,
                   ',',
                   1,
                   20
               )
             - 1
         )
             sat_3,
         SUBSTR(
             month_string,
               INSTR(
                   month_string,
                   ',',
                   1,
                   21
               )
             + 1,
               INSTR(
                   month_string,
                   ',',
                   1,
                   22
               )
             - INSTR(
                   month_string,
                   ',',
                   1,
                   21
               )
             - 1
         )
             sun_4,
         SUBSTR(
             month_string,
               INSTR(
                   month_string,
                   ',',
                   1,
                   22
               )
             + 1,
               INSTR(
                   month_string,
                   ',',
                   1,
                   23
               )
             - INSTR(
                   month_string,
                   ',',
                   1,
                   22
               )
             - 1
         )
             mon_4,
         SUBSTR(
             month_string,
               INSTR(
                   month_string,
                   ',',
                   1,
                   23
               )
             + 1,
               INSTR(
                   month_string,
                   ',',
                   1,
                   24
               )
             - INSTR(
                   month_string,
                   ',',
                   1,
                   23
               )
             - 1
         )
             tue_4,
         SUBSTR(
             month_string,
               INSTR(
                   month_string,
                   ',',
                   1,
                   24
               )
             + 1,
               INSTR(
                   month_string,
                   ',',
                   1,
                   25
               )
             - INSTR(
                   month_string,
                   ',',
                   1,
                   24
               )
             - 1
         )
             wed_4,
         SUBSTR(
             month_string,
               INSTR(
                   month_string,
                   ',',
                   1,
                   25
               )
             + 1,
               INSTR(
                   month_string,
                   ',',
                   1,
                   26
               )
             - INSTR(
                   month_string,
                   ',',
                   1,
                   25
               )
             - 1
         )
             thu_4,
         SUBSTR(
             month_string,
               INSTR(
                   month_string,
                   ',',
                   1,
                   26
               )
             + 1,
               INSTR(
                   month_string,
                   ',',
                   1,
                   27
               )
             - INSTR(
                   month_string,
                   ',',
                   1,
                   26
               )
             - 1
         )
             fri_4,
         SUBSTR(
             month_string,
               INSTR(
                   month_string,
                   ',',
                   1,
                   27
               )
             + 1,
               INSTR(
                   month_string,
                   ',',
                   1,
                   28
               )
             - INSTR(
                   month_string,
                   ',',
                   1,
                   27
               )
             - 1
         )
             sat_4,
         SUBSTR(
             month_string,
               INSTR(
                   month_string,
                   ',',
                   1,
                   28
               )
             + 1,
               INSTR(
                   month_string,
                   ',',
                   1,
                   29
               )
             - INSTR(
                   month_string,
                   ',',
                   1,
                   28
               )
             - 1
         )
             sun_5,
         SUBSTR(
             month_string,
               INSTR(
                   month_string,
                   ',',
                   1,
                   29
               )
             + 1,
               INSTR(
                   month_string,
                   ',',
                   1,
                   30
               )
             - INSTR(
                   month_string,
                   ',',
                   1,
                   29
               )
             - 1
         )
             mon_5,
         SUBSTR(
             month_string,
               INSTR(
                   month_string,
                   ',',
                   1,
                   30
               )
             + 1,
               INSTR(
                   month_string,
                   ',',
                   1,
                   31
               )
             - INSTR(
                   month_string,
                   ',',
                   1,
                   30
               )
             - 1
         )
             tue_5,
         SUBSTR(
             month_string,
               INSTR(
                   month_string,
                   ',',
                   1,
                   31
               )
             + 1,
               INSTR(
                   month_string,
                   ',',
                   1,
                   32
               )
             - INSTR(
                   month_string,
                   ',',
                   1,
                   31
               )
             - 1
         )
             wed_5,
         SUBSTR(
             month_string,
               INSTR(
                   month_string,
                   ',',
                   1,
                   32
               )
             + 1,
               INSTR(
                   month_string,
                   ',',
                   1,
                   33
               )
             - INSTR(
                   month_string,
                   ',',
                   1,
                   32
               )
             - 1
         )
             thu_5,
         SUBSTR(
             month_string,
               INSTR(
                   month_string,
                   ',',
                   1,
                   33
               )
             + 1,
               INSTR(
                   month_string,
                   ',',
                   1,
                   34
               )
             - INSTR(
                   month_string,
                   ',',
                   1,
                   33
               )
             - 1
         )
             fri_5,
         SUBSTR(
             month_string,
               INSTR(
                   month_string,
                   ',',
                   1,
                   34
               )
             + 1,
               INSTR(
                   month_string,
                   ',',
                   1,
                   35
               )
             - INSTR(
                   month_string,
                   ',',
                   1,
                   34
               )
             - 1
         )
             sat_5,
         SUBSTR(
             month_string,
               INSTR(
                   month_string,
                   ',',
                   1,
                   35
               )
             + 1,
               INSTR(
                   month_string,
                   ',',
                   1,
                   36
               )
             - INSTR(
                   month_string,
                   ',',
                   1,
                   35
               )
             - 1
         )
             sun_6,
         SUBSTR(
             month_string,
               INSTR(
                   month_string,
                   ',',
                   1,
                   36
               )
             + 1,
               INSTR(
                   month_string,
                   ',',
                   1,
                   37
               )
             - INSTR(
                   month_string,
                   ',',
                   1,
                   36
               )
             - 1
         )
             mon_6,
         SUBSTR(
             month_string,
               INSTR(
                   month_string,
                   ',',
                   1,
                   37
               )
             + 1,
               INSTR(
                   month_string,
                   ',',
                   1,
                   38
               )
             - INSTR(
                   month_string,
                   ',',
                   1,
                   37
               )
             - 1
         )
             tue_6
    FROM (SELECT ym,
                 customer_id,
                 patient_name,
                    CASE
                        WHEN TO_DATE(ym, 'yyyymm') - TRUNC(TO_DATE(ym, 'yyyymm'), 'iw') + 1 < 7
                        THEN
                            RPAD(
                                ',',
                                (TO_DATE(ym, 'yyyymm') - TRUNC(TO_DATE(ym, 'yyyymm'), 'iw') + 1),
                                ','
                            )
                    END
                 || days_in_month
                 || RPAD(
                        ',',
                        (  LENGTH(
                               REPLACE(
                                      CASE
                                          WHEN   TO_DATE(ym, 'yyyymm')
                                               - TRUNC(TO_DATE(ym, 'yyyymm'), 'iw')
                                               + 1 < 7
                                          THEN
                                              RPAD(
                                                  ',',
                                                  (  TO_DATE(ym, 'yyyymm')
                                                   - TRUNC(TO_DATE(ym, 'yyyymm'), 'iw')
                                                   + 1),
                                                  ','
                                              )
                                      END
                                   || days_in_month,
                                   ','
                               )
                           )
                         - LENGTH(
                                  CASE
                                      WHEN   TO_DATE(ym, 'yyyymm')
                                           - TRUNC(TO_DATE(ym, 'yyyymm'), 'iw')
                                           + 1 < 7
                                      THEN
                                          RPAD(
                                              ',',
                                              (  TO_DATE(ym, 'yyyymm')
                                               - TRUNC(TO_DATE(ym, 'yyyymm'), 'iw')
                                               + 1),
                                              ','
                                          )
                                  END
                               || days_in_month
                           )
                         + 38),
                        ','
                    )
                     month_string
            FROM (  SELECT TO_CHAR(TRUNC(ih.invoice_date, 'mm'), 'yyyymm') ym,
                           ih.customer_id,
                           patient_name,
                              TO_CHAR(
                                  COUNT(CASE WHEN TO_CHAR(ih.invoice_date, 'dd') = '01' THEN 1 END)
                              )
                           || ','
                           || TO_CHAR(
                                  COUNT(CASE WHEN TO_CHAR(ih.invoice_date, 'dd') = '02' THEN 1 END)
                              )
                           || ','
                           || TO_CHAR(
                                  COUNT(CASE WHEN TO_CHAR(ih.invoice_date, 'dd') = '03' THEN 1 END)
                              )
                           || ','
                           || TO_CHAR(
                                  COUNT(CASE WHEN TO_CHAR(ih.invoice_date, 'dd') = '04' THEN 1 END)
                              )
                           || ','
                           || TO_CHAR(
                                  COUNT(CASE WHEN TO_CHAR(ih.invoice_date, 'dd') = '05' THEN 1 END)
                              )
                           || ','
                           || TO_CHAR(
                                  COUNT(CASE WHEN TO_CHAR(ih.invoice_date, 'dd') = '06' THEN 1 END)
                              )
                           || ','
                           || TO_CHAR(
                                  COUNT(CASE WHEN TO_CHAR(ih.invoice_date, 'dd') = '07' THEN 1 END)
                              )
                           || ','
                           || TO_CHAR(
                                  COUNT(CASE WHEN TO_CHAR(ih.invoice_date, 'dd') = '08' THEN 1 END)
                              )
                           || ','
                           || TO_CHAR(
                                  COUNT(CASE WHEN TO_CHAR(ih.invoice_date, 'dd') = '09' THEN 1 END)
                              )
                           || ','
                           || TO_CHAR(
                                  COUNT(CASE WHEN TO_CHAR(ih.invoice_date, 'dd') = '10' THEN 1 END)
                              )
                           || ','
                           || TO_CHAR(
                                  COUNT(CASE WHEN TO_CHAR(ih.invoice_date, 'dd') = '11' THEN 1 END)
                              )
                           || ','
                           || TO_CHAR(
                                  COUNT(CASE WHEN TO_CHAR(ih.invoice_date, 'dd') = '12' THEN 1 END)
                              )
                           || ','
                           || TO_CHAR(
                                  COUNT(CASE WHEN TO_CHAR(ih.invoice_date, 'dd') = '13' THEN 1 END)
                              )
                           || ','
                           || TO_CHAR(
                                  COUNT(CASE WHEN TO_CHAR(ih.invoice_date, 'dd') = '14' THEN 1 END)
                              )
                           || ','
                           || TO_CHAR(
                                  COUNT(CASE WHEN TO_CHAR(ih.invoice_date, 'dd') = '15' THEN 1 END)
                              )
                           || ','
                           || TO_CHAR(
                                  COUNT(CASE WHEN TO_CHAR(ih.invoice_date, 'dd') = '16' THEN 1 END)
                              )
                           || ','
                           || TO_CHAR(
                                  COUNT(CASE WHEN TO_CHAR(ih.invoice_date, 'dd') = '17' THEN 1 END)
                              )
                           || ','
                           || TO_CHAR(
                                  COUNT(CASE WHEN TO_CHAR(ih.invoice_date, 'dd') = '18' THEN 1 END)
                              )
                           || ','
                           || TO_CHAR(
                                  COUNT(CASE WHEN TO_CHAR(ih.invoice_date, 'dd') = '19' THEN 1 END)
                              )
                           || ','
                           || TO_CHAR(
                                  COUNT(CASE WHEN TO_CHAR(ih.invoice_date, 'dd') = '20' THEN 1 END)
                              )
                           || ','
                           || TO_CHAR(
                                  COUNT(CASE WHEN TO_CHAR(ih.invoice_date, 'dd') = '21' THEN 1 END)
                              )
                           || ','
                           || TO_CHAR(
                                  COUNT(CASE WHEN TO_CHAR(ih.invoice_date, 'dd') = '22' THEN 1 END)
                              )
                           || ','
                           || TO_CHAR(
                                  COUNT(CASE WHEN TO_CHAR(ih.invoice_date, 'dd') = '23' THEN 1 END)
                              )
                           || ','
                           || TO_CHAR(
                                  COUNT(CASE WHEN TO_CHAR(ih.invoice_date, 'dd') = '24' THEN 1 END)
                              )
                           || ','
                           || TO_CHAR(
                                  COUNT(CASE WHEN TO_CHAR(ih.invoice_date, 'dd') = '24' THEN 1 END)
                              )
                           || ','
                           || TO_CHAR(
                                  COUNT(CASE WHEN TO_CHAR(ih.invoice_date, 'dd') = '25' THEN 1 END)
                              )
                           || ','
                           || TO_CHAR(
                                  COUNT(CASE WHEN TO_CHAR(ih.invoice_date, 'dd') = '27' THEN 1 END)
                              )
                           || ','
                           || TO_CHAR(
                                  COUNT(CASE WHEN TO_CHAR(ih.invoice_date, 'dd') = '28' THEN 1 END)
                              )
                           || ','
                           || TO_CHAR(
                                  COUNT(CASE WHEN TO_CHAR(ih.invoice_date, 'dd') = '29' THEN 1 END)
                              )
                           || ','
                           || TO_CHAR(
                                  COUNT(CASE WHEN TO_CHAR(ih.invoice_date, 'dd') = '30' THEN 1 END)
                              )
                           || ','
                           || TO_CHAR(
                                  COUNT(CASE WHEN TO_CHAR(ih.invoice_date, 'dd') = '31' THEN 1 END)
                              )
                           || ','
                               days_in_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( :date_from, 'DD-MON-YYYY')
                                               AND TO_DATE( :date_to, 'DD-MON-YYYY')
                       AND vp.inv_product_type IN ('RBC',
                                                   'LRBC',
                                                   'LPHER',
                                                   'PHER',
                                                   'FFP',
                                                   'FP24',
                                                   'CRYO')
                  GROUP BY ih.customer_id, patient_name, TRUNC(ih.invoice_date, 'mm')))
ORDER BY ym, customer_id, patient_name

Open in new window

0
sdstuberCommented:
Here's a little simpler version of the query.  It doesn't do all of the aggregating and has fewer date/string conversions.

  SELECT ym,
         customer_id,
         patient_name,
         SUM(
             CASE
                 WHEN iwd = TRUNC(invoice_date, 'mm')
                 THEN
                     CASE WHEN iwd = invoice_date THEN 1 ELSE 0 END
             END
         )
             sun_1,
         SUM(
             CASE
                 WHEN iwd + 1 >= TRUNC(invoice_date, 'mm')
                 THEN
                     CASE WHEN iwd + 1 = invoice_date THEN 1 ELSE 0 END
             END
         )
             mon_1,
         SUM(
             CASE
                 WHEN iwd + 2 >= TRUNC(invoice_date, 'mm')
                 THEN
                     CASE WHEN iwd + 2 = invoice_date THEN 1 ELSE 0 END
             END
         )
             tue_1,
         SUM(
             CASE
                 WHEN iwd + 3 >= TRUNC(invoice_date, 'mm')
                 THEN
                     CASE WHEN iwd + 3 = invoice_date THEN 1 ELSE 0 END
             END
         )
             wed_1,
         SUM(
             CASE
                 WHEN iwd + 4 >= TRUNC(invoice_date, 'mm')
                 THEN
                     CASE WHEN iwd + 4 = invoice_date THEN 1 ELSE 0 END
             END
         )
             thu_1,
         SUM(
             CASE
                 WHEN iwd + 5 >= TRUNC(invoice_date, 'mm')
                 THEN
                     CASE WHEN iwd + 5 = invoice_date THEN 1 ELSE 0 END
             END
         )
             fri_1,
         SUM(CASE WHEN iwd + 6 = invoice_date THEN 1 ELSE 0 END) sat_1,
         SUM(CASE WHEN iwd + 7 = invoice_date THEN 1 ELSE 0 END) sun_2,
         SUM(CASE WHEN iwd + 8 = invoice_date THEN 1 ELSE 0 END) mon_2,
         SUM(CASE WHEN iwd + 9 = invoice_date THEN 1 ELSE 0 END) tue_2,
         SUM(CASE WHEN iwd + 10 = invoice_date THEN 1 ELSE 0 END) wed_2,
         SUM(CASE WHEN iwd + 11 = invoice_date THEN 1 ELSE 0 END) thu_2,
         SUM(CASE WHEN iwd + 12 = invoice_date THEN 1 ELSE 0 END) fri_2,
         SUM(CASE WHEN iwd + 13 = invoice_date THEN 1 ELSE 0 END) sat_2,
         SUM(CASE WHEN iwd + 14 = invoice_date THEN 1 ELSE 0 END) sun_3,
         SUM(CASE WHEN iwd + 15 = invoice_date THEN 1 ELSE 0 END) mon_3,
         SUM(CASE WHEN iwd + 16 = invoice_date THEN 1 ELSE 0 END) tue_3,
         SUM(CASE WHEN iwd + 17 = invoice_date THEN 1 ELSE 0 END) wed_3,
         SUM(CASE WHEN iwd + 18 = invoice_date THEN 1 ELSE 0 END) thu_3,
         SUM(CASE WHEN iwd + 19 = invoice_date THEN 1 ELSE 0 END) fri_3,
         SUM(CASE WHEN iwd + 20 = invoice_date THEN 1 ELSE 0 END) sat_3,
         SUM(CASE WHEN iwd + 21 = invoice_date THEN 1 ELSE 0 END) sun_4,
         SUM(CASE WHEN iwd + 22 = invoice_date THEN 1 ELSE 0 END) mon_4,
         SUM(CASE WHEN iwd + 23 = invoice_date THEN 1 ELSE 0 END) tue_4,
         SUM(CASE WHEN iwd + 24 = invoice_date THEN 1 ELSE 0 END) wed_4,
         SUM(CASE WHEN iwd + 25 = invoice_date THEN 1 ELSE 0 END) thu_4,
         SUM(CASE WHEN iwd + 26 = invoice_date THEN 1 ELSE 0 END) fri_4,
         SUM(CASE WHEN iwd + 27 = invoice_date THEN 1 ELSE 0 END) sat_4,
         SUM(
             CASE
                 WHEN iwd + 28 <= LAST_DAY(invoice_date)
                 THEN
                     CASE WHEN iwd + 28 = invoice_date THEN 1 ELSE 0 END
             END
         )
             sun_5,
         SUM(
             CASE
                 WHEN iwd + 29 <= LAST_DAY(invoice_date)
                 THEN
                     CASE WHEN iwd + 29 = invoice_date THEN 1 ELSE 0 END
             END
         )
             mon_5,
         SUM(
             CASE
                 WHEN iwd + 30 <= LAST_DAY(invoice_date)
                 THEN
                     CASE WHEN iwd + 30 = invoice_date THEN 1 ELSE 0 END
             END
         )
             tue_5,
         SUM(
             CASE
                 WHEN iwd + 31 <= LAST_DAY(invoice_date)
                 THEN
                     CASE WHEN iwd + 31 = invoice_date THEN 1 ELSE 0 END
             END
         )
             wed_5,
         SUM(
             CASE
                 WHEN iwd + 32 <= LAST_DAY(invoice_date)
                 THEN
                     CASE WHEN iwd + 32 = invoice_date THEN 1 ELSE 0 END
             END
         )
             thu_5,
         SUM(
             CASE
                 WHEN iwd + 33 <= LAST_DAY(invoice_date)
                 THEN
                     CASE WHEN iwd + 33 = invoice_date THEN 1 ELSE 0 END
             END
         )
             fri_5,
         SUM(
             CASE
                 WHEN iwd + 34 <= LAST_DAY(invoice_date)
                 THEN
                     CASE WHEN iwd + 34 = invoice_date THEN 1 ELSE 0 END
             END
         )
             sat_5,
         SUM(
             CASE
                 WHEN iwd + 35 <= LAST_DAY(invoice_date)
                 THEN
                     CASE WHEN iwd + 35 = invoice_date THEN 1 ELSE 0 END
             END
         )
             sun_6,
         SUM(
             CASE
                 WHEN iwd + 36 <= LAST_DAY(invoice_date)
                 THEN
                     CASE WHEN iwd + 36 = invoice_date THEN 1 ELSE 0 END
             END
         )
             mon_6,
         SUM(
             CASE
                 WHEN iwd + 37 <= LAST_DAY(invoice_date)
                 THEN
                     CASE WHEN iwd + 37 = invoice_date THEN 1 ELSE 0 END
             END
         )
             tue_6
    FROM (SELECT TO_CHAR(TRUNC(ih.invoice_date, 'mm'), 'yyyymm') ym,
                 NEXT_DAY(TRUNC(ih.invoice_date, 'mm'), 'Sun') - 7 iwd,
                 ih.customer_id,
                 patient_name,
                 ih.invoice_date
            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( :date_from, 'DD-MON-YYYY')
                                     AND TO_DATE( :date_to, 'DD-MON-YYYY')
             AND vp.inv_product_type IN ('RBC',
                                         'LRBC',
                                         'LPHER',
                                         'PHER',
                                         'FFP',
                                         'FP24',
                                         'CRYO'))
GROUP BY ym, customer_id, patient_name
ORDER BY ym, customer_id, patient_name;

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
sdstuberCommented:
Also, is it ever possible to have TUE_6  data?  I left it in the query, but it seems odd.

Assuming a 31 day month starting on a Saturday, that will still only push the last days out til Monday.

Look at August 2015 for an example.
0
anumosesAuthor Commented:
>> Also, is it ever possible to have TUE_6  data?  I left it in the query, but it seems odd.

We will have 5 weeks in a month. That will be the max. sunday being the 1st day of the week.
0
sdstuberCommented:
>>> We will have 5 weeks in a month. That will be the max. sunday being the 1st day of the week.

I don't understand how that answers my question
0
anumosesAuthor Commented:
Looking at Aug 2015 weeks, There are 6 weeks. 1st Aug being a sat( last day of the week). 31st is Monday. So answering to the question, we will not have tue_6 as far as aug 2015 is considered. But I think we can leave that in the query.
0
sdstuberCommented:
>>>  we will not have tue_6 as far as aug 2015 is considered

Aug 2015 is your "best case" scenario for extending into 6 weeks; but it only extends out to Monday.

So,  what is the TUE_6 column for?   It's more calculations and work to produce the result when it's already known that it will never have a value.


If you really want it, then the query should be simplified and change

 SUM(
             CASE
                 WHEN iwd + 37 <= LAST_DAY(invoice_date)
                 THEN
                     CASE WHEN iwd + 37 = invoice_date THEN 1 ELSE 0 END
             END
         )
             tue_6

Open in new window



to simply be this

NULL tue_6

Open in new window



but hopefully that highlights the futility of it
0
anumosesAuthor Commented:
Thanks a lot. I always hope to get answers from you experts.
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.