today:
198
yesterday:
221
Total:
1,109,854

SQL VERY IMPORTANT

admin 2018.06.26 06:29 Views : 275

DECLARE
 SSID2 NUMBER;

BEGIN
delete from MISSIONARY_SUB;
delete from MISSIONARY_SUB2;
delete from MISSIONARY_YEAR;

insert into MISSIONARY_SUB
Select 
MISSIONARY_ID,SUPPORTER_ID,AMOUNT,to_number(to_char(CONTRIBUTION_DATE,'MM')) 
from CONTRIBUTION 
where UPPER(:P9_MISSIONARY_ID) = MISSIONARY_ID 
and :P9_YEAR = to_number(to_char(CONTRIBUTION_DATE,'YYYY'));

insert into MISSIONARY_SUB2
Select 
SSID,SUM(SAMOUNT),SDATE 
from MISSIONARY_SUB 
Group by SSID,SDATE;

INSERT INTO MISSIONARY_YEAR (SID)
SELECT DISTINCT SSID FROM MISSIONARY_SUB;

     FOR x IN (Select * from MISSIONARY_YEAR) LOOP

     UPDATE MISSIONARY_YEAR
     SET JAN = (SELECT SAMOUNT2 FROM MISSIONARY_SUB2 WHERE SSID2 = x.SID AND SDATE2 = 1)
     WHERE SID = x.SID;
     
     UPDATE MISSIONARY_YEAR
     SET FEB = (SELECT SAMOUNT2 FROM MISSIONARY_SUB2 WHERE SSID2 = x.SID AND SDATE2 = 2)
     WHERE SID = x.SID;

     UPDATE MISSIONARY_YEAR
     SET MAR = (SELECT SAMOUNT2 FROM MISSIONARY_SUB2 WHERE SSID2 = x.SID AND SDATE2 = 3)
     WHERE SID = x.SID;
     
     UPDATE MISSIONARY_YEAR
     SET APR = (SELECT SAMOUNT2 FROM MISSIONARY_SUB2 WHERE SSID2 = x.SID AND SDATE2 = 4)
     WHERE SID = x.SID;

     UPDATE MISSIONARY_YEAR
     SET MAY = (SELECT SAMOUNT2 FROM MISSIONARY_SUB2 WHERE SSID2 = x.SID AND SDATE2 = 5)
     WHERE SID = x.SID;
     
     UPDATE MISSIONARY_YEAR
     SET JUN = (SELECT SAMOUNT2 FROM MISSIONARY_SUB2 WHERE SSID2 = x.SID AND SDATE2 = 6)
     WHERE SID = x.SID;

     UPDATE MISSIONARY_YEAR
     SET JUL = (SELECT SAMOUNT2 FROM MISSIONARY_SUB2 WHERE SSID2 = x.SID AND SDATE2 = 7)
     WHERE SID = x.SID;
     
     UPDATE MISSIONARY_YEAR
     SET AUG = (SELECT SAMOUNT2 FROM MISSIONARY_SUB2 WHERE SSID2 = x.SID AND SDATE2 = 8)
     WHERE SID = x.SID;

     UPDATE MISSIONARY_YEAR
     SET SEP = (SELECT SAMOUNT2 FROM MISSIONARY_SUB2 WHERE SSID2 = x.SID AND SDATE2 = 9)
     WHERE SID = x.SID;
     
     UPDATE MISSIONARY_YEAR
     SET OCT = (SELECT SAMOUNT2 FROM MISSIONARY_SUB2 WHERE SSID2 = x.SID AND SDATE2 = 10)
     WHERE SID = x.SID;

     UPDATE MISSIONARY_YEAR
     SET NOV = (SELECT SAMOUNT2 FROM MISSIONARY_SUB2 WHERE SSID2 = x.SID AND SDATE2 = 11)
     WHERE SID = x.SID;
     
     UPDATE MISSIONARY_YEAR
     SET DEC = (SELECT SAMOUNT2 FROM MISSIONARY_SUB2 WHERE SSID2 = x.SID AND SDATE2 = 12)
     WHERE SID = x.SID;

     END LOOP;
COMMIT;
END;