Identify Customers in SQL

Question

The company wish to provide an incentive of free shipping to such customers who have not returned for 2 months. Build a procedure named PROMO_SHIP_SP which determines these customers are and then updates the BB_PROMOLIST table accordingly. The procedure employs the below information:
 
1. Date cutoff = Any customers who do not shopped on the site as this date must be involved as incentive
participants. Utilize the basket creation date to reflect the shopper activity dates.

2. Month = Three-character month (like APR) which must be added to the promotion table to point out which
month the free shipping is obtainable.

3. Year = Four-digit year pointing the year the promotion is efficient

4. PROMO_FLAG = 1 symbolizing free shipping).
 
The BB_PROMOLIST table too has a USED column, that includes a default value of “N” and is updated to a “Y” whenever the shopper employs the promotion. Test the procedure with a cutoff date of 15-FEB-03. Allocate the free shipping for the month of APR and the year of 2003.

Answer 1

create or replace procedure "PROMO_SHIP_SP"(cutoff date)
is
TMON VARCHAR2(3);
TYEAR VARCHAR2(4);
PFLAG VARCHAR2(2);
TUSED VARCHAR2(1);
TID NUMBER;
begin
FOR rset IN (SELECT * FROM BB_SHOPPER where DTENTERED<cutoff)
LOOP
   SELECT count(*) INTO TID FROM BB_PROMOLIST WHERE IDSHOPPER =rset.IDSHOPPER;
   TUSED :='N';
   PFLAG :='0';
  
   IF TID <> 0 THEN      
       TMON :=TO_CHAR (TO_DATE(rset.DTENTERED, 'DD-MM-YYYY'),'MON');
       TYEAR :=TO_CHAR (TO_DATE(rset.DTENTERED, 'DD-MM-YYYY'),'YYYY');
       IF TMON='APR' and TYEAR='2003' THEN
              PFLAG :='1';
       END IF;
       UPDATE BB_PROMOLIST SET MONTH=TMON,YEAR=TYEAR,PROMO_FLAG=PFLAG,USED=TUSED WHERE IDSHOPPER=rset.IDSHOPPER;
   END IF;
END LOOP;

EXCEPTION
WHEN OTHERS THEN
   raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
end;