On May 8, 8:21 am, gazzag <gar...@[EMAIL PROTECTED]
> wrote:
> On 8 May, 13:33, "ame...@[EMAIL PROTECTED]
" <ame...@[EMAIL PROTECTED]
> 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');
|
