Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Two records a year apart
On Tue, 15 Jan 2002 16:42:34 GMT, "Philip Morrow"
<cracker_at_mymorrow.com> wrote:
>I've got a table similar to this
>
>mperiod date
>mstore number
>tc number
>tq number
>tp number
>comp number
>
>I'm using this SQL to determine if, for any one record of say store 154 if
>there is a corisponding record for that store one year prior. It seems to
>be taking a long time... any suggestions on a different way to do this would
>help, or suggestions on a better way to write this SQL would help too.
>
>The explain plan lists two full table accesses.
>
>update mcd.sumps a set a.comp = 1 where a.mstore in (
> select b.mstore from mcd.sumps b where (to_date(a.mperiod) -
>to_date(b.mperiod)) = 365);
>
update mcd.sumps a
set a.comp = 1
where exists
(select 'x'
from mcd.sumps b
where b.mstore = a.mstora
and b.mperiod < add_months(a.mperiod, -12)
The add_months function also will compensate for leap years.
Hth
Sybrand Bakker, Senior Oracle DBA
To reply remove -verwijderdit from my e-mail address Received on Tue Jan 15 2002 - 11:36:30 CST