Re: query

On May 8, 7:33 am, "ame..._at_iwc.net" <ame..._at_iwc.net> wrote:
> On May 8, 6:32 am, gazzag <gar..._at_jamms.org> wrote:
>
>
>
> > On 7 May, 18:50, "ame..._at_iwc.net" <ame..._at_iwc.net> wrote:
>
> > > Thanks for the feedback Mark. Say I have this data:
>
> > > Customer Id Action Date Status
> > > ---------------------------------------
> > > 12345678 12/01/2005 Active
> > > 12345678 03/01/2005 Inactive
> > > 12345678 01/01/2005 Active
> > > 24568123 11/15/2005 Inactive
> > > 33445566 03/01/2006 Active
> > > 32548798 02/28/2005 Active
> > > 77777733 02/15/2005 Inactive
> > > 77777733 02/01/2005 Active
>
> > > Now, basically I want to ignore row# 5 as it falls outside my range.
> > > I also want to ignore row 4 as his status is inactive.
> > > I want to include rows 6 as he is active, and row 1, as his MAX date
> > > shows him active.
>
> > > That is the key, that his MAX date still shows him active.
> > > Row #7 will be ignored because his MAX date shows him as inactive....
>
> > > Does that make more sense? And, we are on 10g R2....
>
> > > Thanks again!
>
> > > John
>
> > As Mark said, post the relevant CREATE TABLE script, together with an
> > INSERT script to populate the table and someone will be more inclined
> > to help with your query.
>
> > HTH
>
> > -g
>
> I do not understand why the CREATE table will help here. The three
> columns of interest are above. If you see the CREATE statement, how
> does that help with the query? Those are the only three columns which
> are related here......
>
> Any why the INSERT also?
>
> John

It would help us help you if we could reproduce your problem without having to write everything including the test data ourselves.

I tried yesterday but TOAD crashed on me. The solution I was checking was essentially becoming a series of nested queries. First is one to find the max date for each customer (simple group by). Using that as a view, find the Active customers (join view and base table on customer id and where base table status=Active)

Get that written and I think you will be just about there. In the final query, I'll bet you do not need the DISTINCT.

Ed
(that is a clue I often see when people show me queries that do not work. When I see DISTINCT, Most often it means that not all the conditions were included.) Received on Thu May 08 2008 - 08:00:03 CDT