Re: query

From: <amerar_at_iwc.net>
Date: Thu, 8 May 2008 07:40:19 -0700 (PDT)
Message-ID: <7764671e-24a7-4e87-8ba1-79ae79ebbc59@m3g2000hsc.googlegroups.com>

On May 8, 8:21 am, gazzag <gar..._at_jamms.org> wrote:
> On 8 May, 13:33, "ame..._at_iwc.net" <ame..._at_iwc.net> wrote:
>
> > 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- Hide quoted text -
>
> > - Show quoted text -
>
> The scripts would help me to help recreate your problem and allow me
> to test my query. That way, we can be sure that we're singing from
> the same hymn sheet, as it were.
>
> However, if you can't be bothered to do that, I can't be bothered to
> spend any time on it myself, either.
>
> I'm out.
>
> -g

Understood. In the example below, I would want to ignore customer 35666699 because he falls out of the date range, 01/01/2005 - 12/31/2005. I'd ignore customer 21435557 because his status is D. I would include customer 32547687 because his MAX date has a status of A. Likewise, I'd want customer 35666687 since his MAX date has a status of A. I would not want customer 22222222 because his MAX date has a status o D.

Hopefully I got it right.......

CREATE TABLE EMAIL_PRODUCT_HIST (

  ACTION_TYPE       VARCHAR2(2),
  STATUS            VARCHAR2(2),
  ACTION_DATE       DATE,
  EMAIL_HIST_ID     NUMBER CONSTRAINT EMAIL_PRODUCT_HIST_HIST_ID_NN
NOT NULL,
  CUSTOMER_ID       NUMBER CONSTRAINT EMAIL_PRODUCT_HIST_CUST_ID_NN

NOT NULL,
  EMAIL_PRODUCT_ID VARCHAR2(5)); INSERT INTO EMAIL_PRODUCT_HIST
VALUES ('H','A',TO_DATE('09032005','MMDDYYYY'),32547687,'PPM'); INSERT INTO EMAIL_PRODUCT_HIST
VALUES ('H','D',TO_DATE('08092005','MMDDYYYY'),32547687,'PPM'); INSERT INTO EMAIL_PRODUCT_HIST
VALUES ('H','A',TO_DATE('03042005','MMDDYYYY'),32547687,'PPM'); INSERT INTO EMAIL_PRODUCT_HIST
VALUES ('H','D',TO_DATE('03042005','MMDDYYYY'),21435557,'PPM'); INSERT INTO EMAIL_PRODUCT_HIST
VALUES ('H','A',TO_DATE('10092005','MMDDYYYY'),35666687,'PPM'); INSERT INTO EMAIL_PRODUCT_HIST
VALUES ('H','A',TO_DATE('12092005','MMDDYYYY'),35666687,'PPM'); INSERT INTO EMAIL_PRODUCT_HIST
VALUES ('H','D',TO_DATE('12152005','MMDDYYYY'),68888687,'PPM'); INSERT INTO EMAIL_PRODUCT_HIST
VALUES ('H','A',TO_DATE('12202006','MMDDYYYY'),35666699,'PPM'); INSERT INTO EMAIL_PRODUCT_HIST
VALUES ('H','A',TO_DATE('03152005','MMDDYYYY'),22222222,'PPM'); INSERT INTO EMAIL_PRODUCT_HIST
VALUES ('H','D',TO_DATE('05202005','MMDDYYYY'),222222222,'PPM'); Received on Thu May 08 2008 - 09:40:19 CDT