week totals and grand totals in the oracle query using UTL_FILE.FOPEN

Since I am getting this report, wanted to know if I can have weekly total in between that can be added in the code itself. Also grand total and the end of all weeks and bottom grand total. Wanted these to be added in the script itself. Help appreciated.

SUN_1 MON_1  TUE_1   WED_1   THU_1  FRI_1   SAT_1  week1total    SUN_2   etc...

CREATE OR REPLACE PROCEDURE load_hosp_usage (date_from IN VARCHAR,
                                                   ym IN VARCHAR)  AS

  FILENAME  UTL_FILE.FILE_TYPE;
  FILENAME1 VARCHAR2(10000);
  v_procedure_c constant varchar2(30) := 'load_hosp_usage';
  v_location_i integer;
	
	
  CURSOR C1 IS
    SELECT inv_product_type,
         customer_id,
         patient_name,
         SUM(
             CASE
                 WHEN iwd = TRUNC(invoice_date, 'mm')
                 THEN
                     CASE WHEN iwd = invoice_date THEN 1 ELSE 0 END
             END
         )
             sun_1,
         SUM(
             CASE
                 WHEN iwd + 1 >= TRUNC(invoice_date, 'mm')
                 THEN
                     CASE WHEN iwd + 1 = invoice_date THEN 1 ELSE 0 END
             END
         )
             mon_1,
         SUM(
             CASE
                 WHEN iwd + 2 >= TRUNC(invoice_date, 'mm')
                 THEN
                     CASE WHEN iwd + 2 = invoice_date THEN 1 ELSE 0 END
             END
         )
             tue_1,
         SUM(
             CASE
                 WHEN iwd + 3 >= TRUNC(invoice_date, 'mm')
                 THEN
                     CASE WHEN iwd + 3 = invoice_date THEN 1 ELSE 0 END
             END
         )
             wed_1,
         SUM(
             CASE
                 WHEN iwd + 4 >= TRUNC(invoice_date, 'mm')
                 THEN
                     CASE WHEN iwd + 4 = invoice_date THEN 1 ELSE 0 END
             END
         )
             thu_1,
         SUM(
             CASE
                 WHEN iwd + 5 >= TRUNC(invoice_date, 'mm')
                 THEN
                     CASE WHEN iwd + 5 = invoice_date THEN 1 ELSE 0 END
             END
         )
             fri_1,
         NVL(SUM(CASE WHEN iwd + 6 = invoice_date THEN 1 ELSE 0 END),0) sat_1,
         NVL(SUM(CASE WHEN iwd + 7 = invoice_date THEN 1 ELSE 0 END),0) sun_2,
         NVL(SUM(CASE WHEN iwd + 8 = invoice_date THEN 1 ELSE 0 END),0) mon_2,
         NVL(SUM(CASE WHEN iwd + 9 = invoice_date THEN 1 ELSE 0 END),0) tue_2,
         NVL(SUM(CASE WHEN iwd + 10 = invoice_date THEN 1 ELSE 0 END),0) wed_2,
         NVL(SUM(CASE WHEN iwd + 11 = invoice_date THEN 1 ELSE 0 END),0) thu_2,
         NVL(SUM(CASE WHEN iwd + 12 = invoice_date THEN 1 ELSE 0 END),0) fri_2,
         NVL(SUM(CASE WHEN iwd + 13 = invoice_date THEN 1 ELSE 0 END),0) sat_2,
         NVL(SUM(CASE WHEN iwd + 14 = invoice_date THEN 1 ELSE 0 END),0) sun_3,
         NVL(SUM(CASE WHEN iwd + 15 = invoice_date THEN 1 ELSE 0 END),0) mon_3,
         NVL(SUM(CASE WHEN iwd + 16 = invoice_date THEN 1 ELSE 0 END),0) tue_3,
         NVL(SUM(CASE WHEN iwd + 17 = invoice_date THEN 1 ELSE 0 END),0) wed_3,
         NVL(SUM(CASE WHEN iwd + 18 = invoice_date THEN 1 ELSE 0 END),0) thu_3,
         NVL(SUM(CASE WHEN iwd + 19 = invoice_date THEN 1 ELSE 0 END),0) fri_3,
         NVL(SUM(CASE WHEN iwd + 20 = invoice_date THEN 1 ELSE 0 END),0) sat_3,
         NVL(SUM(CASE WHEN iwd + 21 = invoice_date THEN 1 ELSE 0 END),0) sun_4,
         NVL(SUM(CASE WHEN iwd + 22 = invoice_date THEN 1 ELSE 0 END),0) mon_4,
         NVL(SUM(CASE WHEN iwd + 23 = invoice_date THEN 1 ELSE 0 END),0) tue_4,
         NVL(SUM(CASE WHEN iwd + 24 = invoice_date THEN 1 ELSE 0 END),0)wed_4,
         NVL(SUM(CASE WHEN iwd + 25 = invoice_date THEN 1 ELSE 0 END),0) thu_4,
         NVL(SUM(CASE WHEN iwd + 26 = invoice_date THEN 1 ELSE 0 END),0) fri_4,
         NVL(SUM(CASE WHEN iwd + 27 = invoice_date THEN 1 ELSE 0 END),0) sat_4,
         NVL(SUM(CASE WHEN iwd + 28 = invoice_date THEN 1 ELSE 0 END),0) sun_5,
         NVL(SUM(CASE WHEN iwd + 29 = invoice_date THEN 1 ELSE 0 END),0) mon_5,
         NVL(SUM(CASE WHEN iwd + 30 = invoice_date THEN 1 ELSE 0 END),0) tue_5,
         NVL(SUM(CASE WHEN iwd + 31 = invoice_date THEN 1 ELSE 0 END),0) wed_5,
         NVL(SUM(CASE WHEN iwd + 32 = invoice_date THEN 1 ELSE 0 END),0) thu_5,
         NVL(SUM(CASE WHEN iwd + 33 = invoice_date THEN 1 ELSE 0 END),0) fri_5,
         NVL(SUM(CASE WHEN iwd + 34 = invoice_date THEN 1 ELSE 0 END),0) sat_5,
         NVL(SUM(CASE WHEN iwd + 35 = invoice_date THEN 1 ELSE 0 END),0) sun_6,
         NVL(SUM(CASE WHEN iwd + 36 = invoice_date THEN 1 ELSE 0 END),0) mon_6,
         NVL(SUM(CASE WHEN iwd + 37 = invoice_date THEN 1 ELSE 0 END),0) tue_6,
         NVL(SUM(CASE WHEN iwd + 38 = invoice_date THEN 1 ELSE 0 END),0) wed_6,
         NVL(SUM(CASE WHEN iwd + 39 = invoice_date THEN 1 ELSE 0 END),0) thu_6,
         NVL(SUM(CASE WHEN iwd + 40 = invoice_date THEN 1 ELSE 0 END),0) fri_6,
         NVL(SUM(CASE WHEN iwd + 41 = invoice_date THEN 1 ELSE 0 END),0) sat_6,
         NVL(SUM(CASE WHEN iwd + 42 = invoice_date THEN 1 ELSE 0 END),0) sun_7,
         NVL(SUM(CASE WHEN iwd + 43 = invoice_date THEN 1 ELSE 0 END),0) mon_7,
         NVL(SUM(CASE WHEN iwd + 44 = invoice_date THEN 1 ELSE 0 END),0) tue_7,
         NVL(SUM(CASE WHEN iwd + 45 = invoice_date THEN 1 ELSE 0 END),0) wed_7,
         NVL(SUM(CASE WHEN iwd + 46 = invoice_date THEN 1 ELSE 0 END),0) thu_7,
         NVL(SUM(CASE WHEN iwd + 47 = invoice_date THEN 1 ELSE 0 END),0) fri_7,
         NVL(SUM(CASE WHEN iwd + 48 = invoice_date THEN 1 ELSE 0 END),0) sat_7,
         NVL(SUM(CASE WHEN iwd + 49 = invoice_date THEN 1 ELSE 0 END),0)sun_8,
         NVL(SUM(CASE WHEN iwd + 50 = invoice_date THEN 1 ELSE 0 END),0) mon_8,
         NVL(SUM(CASE WHEN iwd + 51 = invoice_date THEN 1 ELSE 0 END),0) tue_8,
         NVL(SUM(CASE WHEN iwd + 52 = invoice_date THEN 1 ELSE 0 END),0)wed_8,
         NVL(SUM(CASE WHEN iwd + 53 = invoice_date THEN 1 ELSE 0 END),0) thu_8,
         NVL(SUM(CASE WHEN iwd + 54 = invoice_date THEN 1 ELSE 0 END),0) fri_8,
         NVL(SUM(CASE WHEN iwd + 55 = invoice_date THEN 1 ELSE 0 END),0) sat_8,
         NVL(SUM(CASE WHEN iwd + 56 = invoice_date THEN 1 ELSE 0 END),0) sun_9,
         NVL(SUM(CASE WHEN iwd + 57 = invoice_date THEN 1 ELSE 0 END),0) mon_9,
         NVL(SUM(CASE WHEN iwd + 58 = invoice_date THEN 1 ELSE 0 END),0) tue_9,
         NVL(SUM(CASE WHEN iwd + 59 = invoice_date THEN 1 ELSE 0 END),0) wed_9,
         NVL(SUM(CASE WHEN iwd + 60 = invoice_date THEN 1 ELSE 0 END),0) thu_9,
         NVL(SUM(CASE WHEN iwd + 61 = invoice_date THEN 1 ELSE 0 END),0) fri_9,
         NVL(SUM(CASE WHEN iwd + 62 = invoice_date THEN 1 ELSE 0 END),0) sat_9,
         NVL(SUM(CASE WHEN iwd + 63 = invoice_date THEN 1 ELSE 0 END),0) sun_10,
         NVL(SUM(CASE WHEN iwd + 64 = invoice_date THEN 1 ELSE 0 END),0) mon_10,
         NVL(SUM(CASE WHEN iwd + 65 = invoice_date THEN 1 ELSE 0 END),0) tue_10,
         NVL(SUM(CASE WHEN iwd + 66 = invoice_date THEN 1 ELSE 0 END),0) wed_10,
         NVL(SUM(CASE WHEN iwd + 67 = invoice_date THEN 1 ELSE 0 END),0) thu_10,
         NVL(SUM(CASE WHEN iwd + 68 = invoice_date THEN 1 ELSE 0 END),0) fri_10,
         NVL(SUM(CASE WHEN iwd + 69 = invoice_date THEN 1 ELSE 0 END),0) sat_10,
         NVL(SUM(CASE WHEN iwd + 70 = invoice_date THEN 1 ELSE 0 END),0) sun_11,
         NVL(SUM(CASE WHEN iwd + 71 = invoice_date THEN 1 ELSE 0 END),0) mon_11,
         NVL(SUM(CASE WHEN iwd + 72 = invoice_date THEN 1 ELSE 0 END),0) tue_11,
         NVL(SUM(CASE WHEN iwd + 73 = invoice_date THEN 1 ELSE 0 END),0) wed_11,
         NVL(SUM(CASE WHEN iwd + 74 = invoice_date THEN 1 ELSE 0 END),0) thu_11,
         NVL(SUM(CASE WHEN iwd + 75 = invoice_date THEN 1 ELSE 0 END),0) fri_11,
         NVL(SUM(CASE WHEN iwd + 76 = invoice_date THEN 1 ELSE 0 END),0) sat_11,
         NVL(SUM(CASE WHEN iwd + 77 = invoice_date THEN 1 ELSE 0 END),0) sun_12,
         NVL(SUM(CASE WHEN iwd + 78 = invoice_date THEN 1 ELSE 0 END),0) mon_12,
         NVL(SUM(CASE WHEN iwd + 79 = invoice_date THEN 1 ELSE 0 END),0) tue_12,
         NVL(SUM(CASE WHEN iwd + 80 = invoice_date THEN 1 ELSE 0 END),0) wed_12,
         NVL(SUM(CASE WHEN iwd + 81 = invoice_date THEN 1 ELSE 0 END),0) thu_12,
         NVL(SUM(CASE WHEN iwd + 82 = invoice_date THEN 1 ELSE 0 END),0) fri_12,
         NVL(SUM(CASE WHEN iwd + 83 = invoice_date THEN 1 ELSE 0 END),0) sat_12,
         NVL(SUM(CASE WHEN iwd + 84 = invoice_date THEN 1 ELSE 0 END),0) sun_13,
         NVL(SUM(CASE WHEN iwd + 85 = invoice_date THEN 1 ELSE 0 END),0) mon_13,
         NVL(SUM(CASE WHEN iwd + 86 = invoice_date THEN 1 ELSE 0 END),0) tue_13,
         NVL(SUM(CASE WHEN iwd + 87 = invoice_date THEN 1 ELSE 0 END),0) wed_13,
         NVL(SUM(CASE WHEN iwd + 88 = invoice_date THEN 1 ELSE 0 END),0) thu_13,
         NVL(SUM(CASE WHEN iwd + 89 = invoice_date THEN 1 ELSE 0 END),0) fri_13,
         NVL(SUM(CASE WHEN iwd + 90 = invoice_date THEN 1 ELSE 0 END),0) sat_13,
         NVL(SUM(CASE WHEN iwd + 91 = invoice_date THEN 1 ELSE 0 END),0) sun_14,
         NVL(SUM(CASE WHEN iwd + 92 = invoice_date THEN 1 ELSE 0 END),0) mon_14,
         NVL(SUM(CASE WHEN iwd + 93 = invoice_date THEN 1 ELSE 0 END),0) tue_14,
         NVL(SUM(CASE WHEN iwd + 94 = invoice_date THEN 1 ELSE 0 END),0) wed_14,
         NVL(SUM(CASE WHEN iwd + 95 = invoice_date THEN 1 ELSE 0 END),0) thu_14,
         NVL(SUM(CASE WHEN iwd + 96 = invoice_date THEN 1 ELSE 0 END),0) fri_14,
         NVL(SUM(CASE WHEN iwd + 97 = invoice_date THEN 1 ELSE 0 END),0) sat_14,
         NVL(SUM(CASE WHEN iwd + 98 = invoice_date THEN 1 ELSE 0 END),0) sun_15,
         NVL(SUM(CASE WHEN iwd + 99 = invoice_date THEN 1 ELSE 0 END),0) mon_15
    FROM (SELECT TO_CHAR(TRUNC(ih.invoice_date, 'mm'), 'yyyymm') ym,
                 NEXT_DAY(TRUNC(TO_DATE( date_from, 'DD-MON-YYYY'), 'mm'), 'Sun') iwd,
                 CASE
                     WHEN vp.inv_product_type = 'RBC' THEN 'Red Blood Cells'
                     WHEN vp.inv_product_type = 'LRBC' THEN 'Leukoreduced Red Blood Cells'
                     WHEN vp.inv_product_type = 'LPHER' THEN 'SDP'
                     WHEN vp.inv_product_type = 'PHER' THEN 'SDP'
                     WHEN vp.inv_product_type = 'FFP' THEN 'Fresh Frozen Plasma'
                     WHEN vp.inv_product_type = 'FP24' THEN 'Plasma Frozen withing 24hrs'
                     WHEN vp.inv_product_type = 'CRYO' THEN 'Cryoprecipitate'
                 END
                     inv_product_type,
                 ih.customer_id,
                 id.patient_name,
                 ih.invoice_date
            FROM tab1 ih,
                tab2 id,
                 tab4 ip,
                 tab3 vp
           WHERE id.invoice_number = ih.invoice_number
             AND id.item_id = vp.product_code
             AND id.item_id = ip.item_id
             AND ip.item_type IN ('P')
             --AND ih.customer_id = 'WAD-EX0128'
             AND ih.invoice_date >= TO_DATE( ym, 'YYYYMM')
             AND ih.invoice_date < ADD_MONTHS(TO_DATE( ym, 'YYYYMM'), 3)
             AND vp.inv_product_type IN ('RBC',
                                         'LRBC',
                                         'LPHER',
                                         'PHER',
                                         'FFP',
                                         'FP24',
                                         'CRYO'))
GROUP BY inv_product_type, customer_id, patient_name
ORDER BY inv_product_type, customer_id, patient_name;
  VARC1 C1%ROWTYPE;
BEGIN

v_location_i := 1000;

  --FILENAME1   := 'hosp_usage' || '_' || SYSDATE || '.CSV';
  FILENAME1   := 'hosp_usage' || '_' || SYSDATE || '.xls';
v_location_i := 2000;
  FILENAME    := UTL_FILE.FOPEN('HOSP_USAGE_DIR', FILENAME1, 'W');

  /* THIS WILL CREATE THE HEADING IN EXCEL SHEET */
  v_location_i := 3000;
    UTL_FILE.PUT_LINE(FILENAME, 'INV PRODUCT TYPE'||chr(9)||'CUSTOMER ID'||chr(9)||'NAME'||chr(9)||
             'SUN1'||chr(9)||'MON1'||chr(9)||'TUE1'||chr(9)||'WED1'||chr(9)||'THU1'||chr(9)||'FRI1'||chr(9)||'SAT1'||chr(9)||
			 'SUN2'||chr(9)||'MON2'||chr(9)||'TUE2'||chr(9)||'WED2'||chr(9)||'THU2'||chr(9)||'FRI2'||chr(9)||'SAT2'||chr(9)||
			 'SUN3'||chr(9)||'MON3'||chr(9)||'TUE3'||chr(9)||'WED3'||chr(9)||'THU3'||chr(9)||'FRI3'||chr(9)||'SAT3'||chr(9)||
			 'SUN4'||chr(9)||'MON4'||chr(9)||'TUE4'||chr(9)||'WED4'||chr(9)||'THU4'||chr(9)||'FRI4'||chr(9)||'SAT4'||chr(9)||
			 'SUN5'||chr(9)||'MON5'||chr(9)||'TUE5'||chr(9)||'WED5'||chr(9)||'THU5'||chr(9)||'FRI5'||chr(9)||'SAT5'||chr(9)||
			 'SUN6'||chr(9)||'MON6'||chr(9)||'TUE6'||chr(9)||'WED6'||chr(9)||'THU6'||chr(9)||'FRI6'||chr(9)||'SAT6'||chr(9)||
			 'SUN7'||chr(9)||'MON7'||chr(9)||'TUE7'||chr(9)||'WED7'||chr(9)||'THU7'||chr(9)||'FRI7'||chr(9)||'SAT7'||chr(9)||
			 'SUN8'||chr(9)||'MON8'||chr(9)||'TUE8'||chr(9)||'WED8'||chr(9)||'THU8'||chr(9)||'FRI8'||chr(9)||'SAT8'||chr(9)||
			 'SUN9'||chr(9)||'MON9'||chr(9)||'TUE9'||chr(9)||'WED9'||chr(9)||'THU9'||chr(9)||'FRI9'||chr(9)||'SAT9'||chr(9)||
			 'SUN10'||chr(9)||'MON10'||chr(9)||'TUE10'||chr(9)||'WED10'||chr(9)||'THU10'||chr(9)||'FRI10'||chr(9)||'SAT10'||chr(9)||
			 'SUN11'||chr(9)||'MON11'||chr(9)||'TUE11'||chr(9)||'WED11'||chr(9)||'THU11'||chr(9)||'FRI11'||chr(9)||'SAT11'||chr(9)||
			 'SUN12'||chr(9)||'MON12'||chr(9)||'TUE12'||chr(9)||'WED12'||chr(9)||'THU12'||chr(9)||'FRI12'||chr(9)||'SAT12'||chr(9)||
			 'SUN13'||chr(9)||'MON13'||chr(9)||'TUE13'||chr(9)||'WED13'||chr(9)||'THU13'||chr(9)||'FRI13'||chr(9)||'SAT13'||chr(9)||
			 'SUN14'||chr(9)||'MON14'||chr(9)||'TUE14'||chr(9)||'WED14'||chr(9)||'THU14'||chr(9)||'FRI14'||chr(9)||'SAT14'||chr(9)||
			 'SUN15'||chr(9)||'MON15');
	
	
  OPEN C1;
  v_location_i := 4000;
  LOOP
    FETCH C1
       INTO VARC1;
	   v_location_i := 5000;
    EXIT WHEN C1%NOTFOUND;
     /*  THIS WILL PRINT THE RECORDS IN EXCEL SHEET AS PER THE QUERY IN CURSOR */
   v_location_i := 6000;
    UTL_FILE.PUT_LINE(FILENAME,VARC1.inv_product_type||chr(9)||VARC1.customer_id||chr(9)||VARC1.patient_name||chr(9)||
	               VARC1.sun_1||chr(9)||VARC1.mon_1||chr(9)||VARC1.tue_1||chr(9)||VARC1.wed_1||chr(9)||VARC1.thu_1||chr(9)||VARC1.fri_1||chr(9)||VARC1.sat_1||chr(9)||
        		   VARC1.sun_2||chr(9)||VARC1.mon_2||chr(9)||VARC1.tue_2||chr(9)||VARC1.wed_2||chr(9)||VARC1.thu_2||chr(9)||VARC1.fri_2||chr(9)||VARC1.sat_2||chr(9)||
				   VARC1.sun_3||chr(9)||VARC1.mon_3||chr(9)||VARC1.tue_3||chr(9)||VARC1.wed_3||chr(9)||VARC1.thu_3||chr(9)||VARC1.fri_3||chr(9)||VARC1.sat_3||chr(9)||
				   VARC1.sun_4||chr(9)||VARC1.mon_4||chr(9)||VARC1.tue_4||chr(9)||VARC1.wed_4||chr(9)||VARC1.thu_4||chr(9)||VARC1.fri_4||chr(9)||VARC1.sat_4||chr(9)||
				   VARC1.sun_5||chr(9)||VARC1.mon_5||chr(9)||VARC1.tue_5||chr(9)||VARC1.wed_5||chr(9)||VARC1.thu_5||chr(9)||VARC1.fri_5||chr(9)||VARC1.sat_5||chr(9)||
				   VARC1.sun_6||chr(9)||VARC1.mon_6||chr(9)||VARC1.tue_6||chr(9)||VARC1.wed_6||chr(9)||VARC1.thu_6||chr(9)||VARC1.fri_6||chr(9)||VARC1.sat_6||chr(9)||
				   VARC1.sun_7||chr(9)||VARC1.mon_7||chr(9)||VARC1.tue_7||chr(9)||VARC1.wed_7||chr(9)||VARC1.thu_7||chr(9)||VARC1.fri_7||chr(9)||VARC1.sat_7||chr(9)||
				   VARC1.sun_8||chr(9)||VARC1.mon_8||chr(9)||VARC1.tue_8||chr(9)||VARC1.wed_8||chr(9)||VARC1.thu_8||chr(9)||VARC1.fri_8||chr(9)||VARC1.sat_8||chr(9)||
				   VARC1.sun_9||chr(9)||VARC1.mon_9||chr(9)||VARC1.tue_9||chr(9)||VARC1.wed_9||chr(9)||VARC1.thu_9||chr(9)||VARC1.fri_9||chr(9)||VARC1.sat_9||chr(9)||
				   VARC1.sun_10||chr(9)||VARC1.mon_10||chr(9)||VARC1.tue_10||chr(9)||VARC1.wed_10||chr(9)||VARC1.thu_10||chr(9)||VARC1.fri_10||chr(9)||VARC1.sat_10||chr(9)||
				   VARC1.sun_11||chr(9)||VARC1.mon_11||chr(9)||VARC1.tue_11||chr(9)||VARC1.wed_11||chr(9)||VARC1.thu_11||chr(9)||VARC1.fri_11||chr(9)||VARC1.sat_11||chr(9)||
				   VARC1.sun_12||chr(9)||VARC1.mon_12||chr(9)||VARC1.tue_12||chr(9)||VARC1.wed_12||chr(9)||VARC1.thu_12||chr(9)||VARC1.fri_12||chr(9)||VARC1.sat_12||chr(9)||
				   VARC1.sun_13||chr(9)||VARC1.mon_13||chr(9)||VARC1.tue_13||chr(9)||VARC1.wed_13||chr(9)||VARC1.thu_13||chr(9)||VARC1.fri_13||chr(9)||VARC1.sat_13||chr(9)||
				   VARC1.sun_14||chr(9)||VARC1.mon_14||chr(9)||VARC1.tue_14||chr(9)||VARC1.wed_14||chr(9)||VARC1.thu_14||chr(9)||VARC1.fri_14||chr(9)||VARC1.sat_14||chr(9)||
				   VARC1.sun_15||chr(9)||VARC1.mon_15);
				                    
  END LOOP;
v_location_i := 7000;
  UTL_FILE.FCLOSE(FILENAME);
 v_location_i := 8000; 
EXCEPTION
     WHEN OTHERS THEN
      dbms_output.put_line('Error in ' || v_procedure_c ||
                           ' at line: ' || v_location_i); 

END load_hosp_usage;

Open in new window

sample-date.txt
hosp-usage-06-MAY-15.xls
LVL 6
anumosesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Just add them?  It is simple addition.

I would move the individual days to an outer select just to make it cleaner:

CURSOR C1 IS
select inv_product_type,
         customer_id,
         patient_name,
Sun_1,
Mon_1,
Tue_1,
...
Sun_1+Mon_1+Tue_1 .. week_1_total,
...
from (
    SELECT inv_product_type,
         customer_id,
         patient_name,
         SUM(
             CASE
                 WHEN iwd = TRUNC(invoice_date, 'mm')
                 THEN
                     CASE WHEN iwd = invoice_date THEN 1 ELSE 0 END
             END
         )
             sun_1,
         SUM(
             CASE
                 WHEN iwd + 1 >= TRUNC(invoice_date, 'mm')
                 THEN
                     CASE WHEN iwd + 1 = invoice_date THEN 1 ELSE 0 END
             END
         )
             mon_1,
         SUM(
             CASE
                 WHEN iwd + 2 >= TRUNC(invoice_date, 'mm')
                 THEN
                     CASE WHEN iwd + 2 = invoice_date THEN 1 ELSE 0 END
             END
         )
             tue_1,
   ...
)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
anumosesAuthor Commented:
Different. That question was not answered yet. Previous question was  how to give column names as real date? Now I could not do that. So still have SUN_1 etc as column names. But I wrote the code to put the output as excel in external directory. But wanted to add totals in between. So new question.
0
anumosesAuthor Commented:
thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.