Re: Trying to see if I can do the attached without PL/SQL

From: <madhusreeram_at_gmail.com>
Date: Fri, 25 Jan 2008 14:46:40 -0800 (PST)
Message-ID: <8ca86fbb-76c6-44cd-8a0a-d90f92d05fa3@e23g2000prf.googlegroups.com>


On Jan 25, 3:50 pm, JAW <jwill..._at_aglresources.com> wrote:
> REM table q;
>
> create table q ( premise_no number, DUE_ON_DTTM date);
>
> --
>
> Insert into q (PREMISE_NO, DUE_ON_DTTM) Values
> (966034975,TO_DATE('10/01/2007 12:00:00', 'MM/DD/YYYY HH24:MI:SS'));
>
> Insert into q (PREMISE_NO, DUE_ON_DTTM) Values
> (966034975,TO_DATE('11/01/2007 16:00:00', 'MM/DD/YYYY HH24:MI:SS'));
>
> Insert into q (PREMISE_NO, DUE_ON_DTTM) Values
> (966034975,TO_DATE('11/28/2007 23:59:00', 'MM/DD/YYYY HH24:MI:SS'));
>
> COMMIT;
>
> In the above dataset I would want to find all matching PREMISE_NO
> where the DUE_ON_DTTM are within 30 days of each other. So in the
> above the last two rows inserted would be what I would want to
> returned to me for each group of 3 premises because 11-1-2007 and
> 11-28-2007 are within 30 days of each other since the PREMISE_NO is
> the same.
>
> I can do it with PL/SQL or DOTNET code but trying to see if it can be
> done with just analytics and INLINE views.

The easy but probably not efficient way: select a.*
from q a, q b
where a.premise_no=b.premise_no

      and a.rowid!=b.rowid
      and abs(a.due_on_dttm-b.due_on_dttm) <=30
/

the complex way and more efficient way:

select premise_no, DUE_ON_DTtm
from (
select premise_no, DUE_ON_DTtm,

        lag(DUE_ON_DTTM,1,null) over ( partition by premise_no order by DUE_ON_DTTM) prvDUE_ON_DTTM,

        lead(DUE_ON_DTTM,1,null) over ( partition by premise_no order by DUE_ON_DTTM) nxtDUE_ON_DTTM
from q
)
where prvDUE_ON_DTTM is not null
and (abs(prvDUE_ON_DTTM-DUE_ON_DTTM) <=30

        or abs(nxtDUE_ON_DTTM-DUE_ON_DTTM) <=30) ;

I am sure there are more ways.

hth

Madhu Sreeram Received on Fri Jan 25 2008 - 16:46:40 CST

Original text of this message