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','2010/11/01','2010/11/28'); 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','2010/11/28','2011/01/02'); 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(*) as grossOrders ,sum(TOTALPRODUCT) as grossSales ,sum(TOTALTAX) as grossTax FROM orders where orders_id in (select distinct(orders_id) from orderitems where address_id in (select address_id from address where state =state.state)) 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;