PL/SQL code to prepare a report for a news paper whole seller


PL/SQL code to prepare a report for a news paper wholeseller with the following details
WEEK,PURCHASE,SALES,PURCHASE,RETURN,PROFIT,LOSS

  • Cost of paper 60ps
  • Selling prise 758ps
  • Loss on purchase return 30ps



CREATE TABLE NEWS(WEEK INT NOT NULL,PURCHASE INT,SALES INT,RETURN INT,PROFIT NUMBER(10,2),LOSS NUMBER(10,2));
INSERT INTO NEWS(WEEK,PURCHASE,SALES) VALUES(1,100,80);
INSERT INTO NEWS(WEEK,PURCHASE,SALES) VALUES(2,80,75);
INSERT INTO NEWS(WEEK,PURCHASE,SALES) VALUES(3,100,94);
INSERT INTO NEWS(WEEK,PURCHASE,SALES) VALUES(4,100,60);
INSERT INTO NEWS(WEEK,PURCHASE,SALES) VALUES(5,100,77);
SELECT *FROM NEWS;
DECLARE
I INT:=1;
BOT INT;
SID INT;
RTN INT;
PRF NUMBER(10,2);
BEGIN
WHILE I<=5
LOOP
SELECT SALES INTO SID FROM NEWS WHERE WEEK=I;
SELECT PURCHASE INTO BOT FROM NEWS WHERE WEEK=I;
RTN:=BOT-SID;
PRF:=SID*0.75+RTN*0.30-BOT*0.60;
UPDATE NEWS SET PROFIT=PRF,LOSS=0,RETURN=RTN WHERE WEEK =I AND PRF>=0;
UPDATE NEWS SET PROFIT=0,LOSS=-1*PRF,RETURN=RTN WHERE WEEK =I AND PRF<=0;
I:=I+1;
END LOOP;
END;


SELECT *FROM NEWS;

 

 

 

Tables:

before updating

 

1

after executing the calculations

2

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s