set serveroutput on; declare grossorderss number; grossSaless number; grossTaxs number; st varchar2(256); fromDate varchar2(256); toDate varchar2(256); type date_var is table of varchar2(20); from_date date_var; to_date date_var; begin from_date:=date_var('2010/01/04','2010/02/01','2010/03/01','2010/04/05','2010/05/03','2010/05/31','2010/07/05','2010/08/02','2010/08/30','2010/10/04'); to_date := date_var('2010/01/31','2010/02/28','2010/04/04','2010/05/02','2010/05/30','2010/07/04','2010/08/01','2010/08/29','2010/10/03','2010/10/31'); DBMS_OUTPUT.enable; for elem in 1..from_date.count loop fromDate := from_date(elem); toDate := to_date(elem); DBMS_OUTPUT.PUT_LINE('---------------------------'); DBMS_OUTPUT.PUT_LINE(fromDate ||' to '|| toDate); for state in (select distinct(stateprovabbr)as state from stateprov ) loop select grossorders , grossSales , grossTax into grossorderss , grossSaless , grossTaxs from ( SELECT count(*) grossOrders ,sum(TOTALPRODUCT) grossSales ,sum(TOTALTAX) grossTax FROM orders where orders_id in (select orders_id from orderitems oi,address ad where oi.address_id = ad.address_id and ad.state =state.state and to_char(oi.LASTUPDATE,'YYYY/MM/DD') >= fromdate AND to_char(oi.LASTUPDATE,'YYYY/MM/DD') <= todate AND oi.STATUS IN('S','D')) and to_char(LASTUPDATE,'YYYY/MM/DD') >= fromdate AND to_char(LASTUPDATE,'YYYY/MM/DD') <= todate AND STATUS IN('S','D')); DBMS_OUTPUT.PUT_LINE(' '); DBMS_OUTPUT.PUT(state.state); DBMS_OUTPUT.PUT('-'); DBMS_OUTPUT.PUT(grossorderss); DBMS_OUTPUT.PUT(','); DBMS_OUTPUT.PUT(grossSaless); DBMS_OUTPUT.PUT(','); DBMS_OUTPUT.PUT(grossTaxs); st := state.state; end loop; DBMS_OUTPUT.PUT_LINE(' '); end loop; end;