Compute Days Between Ordering and Shipping in SQL

Question

An analyst in the quality assurance office reviews the time lapse between receiving an order and shipping an order. Any orders which have not been shipped in a day of the order being positioned are investigated. Build a function named ORD_SHIP_SF which computes the number of days between the date the basket was made and the shipping date. The function must return a character string which indicates “OK” when the order was shipped in a day or “CHECK” if it was not. The IDSTAGE column of the BB_BASKETSTATUS table ppoints out the item is shipped with a value of 5 and the DTSTAGE column is the shipping date. The TDORDERED column of the BB_BASKET table is the order date. Utilize the function in an anonymous block which uses a host variable to receive the basket id to check basket 3.

Answer 1

create or replace function "ORD_SHIP_SF"
(baskid in NUMBER)
return VARCHAR2
is
NUMDAYS NUMBER;
begin
SELECT TO_DATE(bs.DTSTAGE,'DD-MM-YYYY')-TO_DATE(bb.DTORDERED,'DD-MM-YYYY') INTO NUMDAYS FROM BB_BASKET BB,BB_BASKETSTATUS BS WHERE BB.IDBASKET=BS.IDBASKET and BB.IDBASKET=baskid;
IF NUMDAYS>0 THEN
   RETURN 'OK';
END IF;
RETURN 'CHECK';
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('No Data Found');
      RETURN 'CHECK';
  WHEN OTHERS THEN
      RETURN 'CHECK';
 
end;