| How to optimize this SQL statement [message #229811] |
Tue, 10 April 2007 01:55  |
oralover
Messages: 97
Registered: January 2005
|
Member |
|
|
hi all gurus!
i m using Oracle 10g (9.0.2)
i have a table below
myTable
the_code varchar2(3),
a_date date,
b_date date,
qty number,
rate number(8,2)
the a_date have current date when new record generated and b_date is whatever like '01-jan-2049', when some kind of process done the b_date is updated with to_date(sysdate)
now if records are:
the_code a_date b_date qty rate
-------- --------- --------- -------- --------
001 01-feb-07 01-jan-49 100 10.2 -> say its Opened (not yet processed)
001 01-feb-07 03-feb-07 50 10.2
002 01-feb-07 04-feb-07 125 10.15
002 02-feb-07 01-jan-49 30 10.15 -> say its Opened (not yet processed)
001 02-feb-07 05-feb-07 20 10.2
001 03-feb-07 01-jan-49 25 10.2 -> say its Opened (not yet processed)
002 03-feb-07 08-feb-07 10 10.15
now today is 10-feb-07 and i want Open positions date wise from 01-feb-07 till date (on 01-feb-07 what was the actual Open position - this will include that days processed records, means b_date has changed) one method i used as the date which is going to process
Select the_code, sum(qty) volume, sum(qty*rate) value
from myTable
where a_date < '01-feb-07'
and b_date >= '01-feb-07'
group by the_code
when using this method in HUGE data, its response is very slow and i have to do this Date by Date but i want it to give a Date Range to produce the desired data.
hope to receive an early response.
much thnx in advance.
PS: sorry for my bad english
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
