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?
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.