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

On Jan 25, 5:46 pm, madhusree..._at_gmail.com wrote:
> 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- Hide quoted text -
>
> - Show quoted text -

Thanks!

I will test this.

Will it work with a group of more than 3? Received on Fri Jan 25 2008 - 17:45:50 CST