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
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.
In the previous question of yours my sample tables only had a few rows. That should be all you need.
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.
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.
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.
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.
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.
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_nameASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
>> 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.
We will have 5 weeks in a month. That will be the max. sunday being the 1st day of the week.
>>> 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
I don't understand how that answers my question
ASKER
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks a lot. I always hope to get answers from you experts.
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.