On May 8, 8:21 am, gazzag <gar...@jamms.org> wrote:
> On 8 May, 13:33, "ame...@iwc.net" <ame...@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');