Oracle query

http://www.experts-exchange.com/Database/Oracle/Q_28663209.html

This is closed. A correct solution given and helped by @sdstuber. Thanks for his oracle knowledge.

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,
         SUM(CASE WHEN iwd + 6 = invoice_date THEN 1 ELSE 0 END) sat_1,
         SUM(CASE WHEN iwd + 7 = invoice_date THEN 1 ELSE 0 END) sun_2,
         SUM(CASE WHEN iwd + 8 = invoice_date THEN 1 ELSE 0 END) mon_2,
         SUM(CASE WHEN iwd + 9 = invoice_date THEN 1 ELSE 0 END) tue_2,
         SUM(CASE WHEN iwd + 10 = invoice_date THEN 1 ELSE 0 END) wed_2,
         SUM(CASE WHEN iwd + 11 = invoice_date THEN 1 ELSE 0 END) thu_2,
         SUM(CASE WHEN iwd + 12 = invoice_date THEN 1 ELSE 0 END) fri_2,
         SUM(CASE WHEN iwd + 13 = invoice_date THEN 1 ELSE 0 END) sat_2,
         SUM(CASE WHEN iwd + 14 = invoice_date THEN 1 ELSE 0 END) sun_3,
         SUM(CASE WHEN iwd + 15 = invoice_date THEN 1 ELSE 0 END) mon_3,
         SUM(CASE WHEN iwd + 16 = invoice_date THEN 1 ELSE 0 END) tue_3,
         SUM(CASE WHEN iwd + 17 = invoice_date THEN 1 ELSE 0 END) wed_3,
         SUM(CASE WHEN iwd + 18 = invoice_date THEN 1 ELSE 0 END) thu_3,
         SUM(CASE WHEN iwd + 19 = invoice_date THEN 1 ELSE 0 END) fri_3,
         SUM(CASE WHEN iwd + 20 = invoice_date THEN 1 ELSE 0 END) sat_3,
         SUM(CASE WHEN iwd + 21 = invoice_date THEN 1 ELSE 0 END) sun_4,
         SUM(CASE WHEN iwd + 22 = invoice_date THEN 1 ELSE 0 END) mon_4,
         SUM(CASE WHEN iwd + 23 = invoice_date THEN 1 ELSE 0 END) tue_4,
         SUM(CASE WHEN iwd + 24 = invoice_date THEN 1 ELSE 0 END) wed_4,
         SUM(CASE WHEN iwd + 25 = invoice_date THEN 1 ELSE 0 END) thu_4,
         SUM(CASE WHEN iwd + 26 = invoice_date THEN 1 ELSE 0 END) fri_4,
         SUM(CASE WHEN iwd + 27 = invoice_date THEN 1 ELSE 0 END) sat_4,
         SUM(CASE WHEN iwd + 28 = invoice_date THEN 1 ELSE 0 END) sun_5,
         SUM(CASE WHEN iwd + 29 = invoice_date THEN 1 ELSE 0 END) mon_5,
         SUM(CASE WHEN iwd + 30 = invoice_date THEN 1 ELSE 0 END) tue_5,
         SUM(CASE WHEN iwd + 31 = invoice_date THEN 1 ELSE 0 END) wed_5,
         SUM(CASE WHEN iwd + 32 = invoice_date THEN 1 ELSE 0 END) thu_5,
         SUM(CASE WHEN iwd + 33 = invoice_date THEN 1 ELSE 0 END) fri_5,
         SUM(CASE WHEN iwd + 34 = invoice_date THEN 1 ELSE 0 END) sat_5,
         SUM(CASE WHEN iwd + 35 = invoice_date THEN 1 ELSE 0 END) sun_5,
         SUM(CASE WHEN iwd + 36 = invoice_date THEN 1 ELSE 0 END) mon_6,
         SUM(CASE WHEN iwd + 37 = invoice_date THEN 1 ELSE 0 END) tue_6,
         SUM(CASE WHEN iwd + 38 = invoice_date THEN 1 ELSE 0 END) wed_6,
         SUM(CASE WHEN iwd + 39 = invoice_date THEN 1 ELSE 0 END) thu_6,
         SUM(CASE WHEN iwd + 40 = invoice_date THEN 1 ELSE 0 END) fri_6,
         SUM(CASE WHEN iwd + 41 = invoice_date THEN 1 ELSE 0 END) sat_6,
         SUM(CASE WHEN iwd + 42 = invoice_date THEN 1 ELSE 0 END) sun_7,
         SUM(CASE WHEN iwd + 43 = invoice_date THEN 1 ELSE 0 END) mon_7,
         SUM(CASE WHEN iwd + 44 = invoice_date THEN 1 ELSE 0 END) tue_7,
         SUM(CASE WHEN iwd + 45 = invoice_date THEN 1 ELSE 0 END) wed_7,
         SUM(CASE WHEN iwd + 46 = invoice_date THEN 1 ELSE 0 END) thu_7,
         SUM(CASE WHEN iwd + 47 = invoice_date THEN 1 ELSE 0 END) fri_7,
         SUM(CASE WHEN iwd + 48 = invoice_date THEN 1 ELSE 0 END) sat_7,
         SUM(CASE WHEN iwd + 49 = invoice_date THEN 1 ELSE 0 END) sun_8,
         SUM(CASE WHEN iwd + 50 = invoice_date THEN 1 ELSE 0 END) mon_8,
         SUM(CASE WHEN iwd + 51 = invoice_date THEN 1 ELSE 0 END) tue_8,
         SUM(CASE WHEN iwd + 52 = invoice_date THEN 1 ELSE 0 END) wed_8,
         SUM(CASE WHEN iwd + 53 = invoice_date THEN 1 ELSE 0 END) thu_8,
         SUM(CASE WHEN iwd + 54 = invoice_date THEN 1 ELSE 0 END) fri_8,
         SUM(CASE WHEN iwd + 55 = invoice_date THEN 1 ELSE 0 END) sat_8,
         SUM(CASE WHEN iwd + 56 = invoice_date THEN 1 ELSE 0 END) sun_9,
         SUM(CASE WHEN iwd + 57 = invoice_date THEN 1 ELSE 0 END) mon_9,
         SUM(CASE WHEN iwd + 58 = invoice_date THEN 1 ELSE 0 END) tue_9,
         SUM(CASE WHEN iwd + 59 = invoice_date THEN 1 ELSE 0 END) wed_9,
         SUM(CASE WHEN iwd + 60 = invoice_date THEN 1 ELSE 0 END) thu_9,
         SUM(CASE WHEN iwd + 61 = invoice_date THEN 1 ELSE 0 END) fri_9,
         SUM(CASE WHEN iwd + 62 = invoice_date THEN 1 ELSE 0 END) sat_9,
         SUM(CASE WHEN iwd + 63 = invoice_date THEN 1 ELSE 0 END) sun_10,
         SUM(CASE WHEN iwd + 64 = invoice_date THEN 1 ELSE 0 END) mon_10,
         SUM(CASE WHEN iwd + 65 = invoice_date THEN 1 ELSE 0 END) tue_10,
         SUM(CASE WHEN iwd + 66 = invoice_date THEN 1 ELSE 0 END) wed_10,
         SUM(CASE WHEN iwd + 67 = invoice_date THEN 1 ELSE 0 END) thu_10,
         SUM(CASE WHEN iwd + 68 = invoice_date THEN 1 ELSE 0 END) fri_10,
         SUM(CASE WHEN iwd + 69 = invoice_date THEN 1 ELSE 0 END) sat_10,
         SUM(CASE WHEN iwd + 70 = invoice_date THEN 1 ELSE 0 END) sun_11,
         SUM(CASE WHEN iwd + 71 = invoice_date THEN 1 ELSE 0 END) mon_11,
         SUM(CASE WHEN iwd + 72 = invoice_date THEN 1 ELSE 0 END) tue_11,
         SUM(CASE WHEN iwd + 73 = invoice_date THEN 1 ELSE 0 END) wed_11,
         SUM(CASE WHEN iwd + 74 = invoice_date THEN 1 ELSE 0 END) thu_11,
         SUM(CASE WHEN iwd + 75 = invoice_date THEN 1 ELSE 0 END) fri_11,
         SUM(CASE WHEN iwd + 76 = invoice_date THEN 1 ELSE 0 END) sat_11,
         SUM(CASE WHEN iwd + 77 = invoice_date THEN 1 ELSE 0 END) sun_12,
         SUM(CASE WHEN iwd + 78 = invoice_date THEN 1 ELSE 0 END) mon_12,
         SUM(CASE WHEN iwd + 79 = invoice_date THEN 1 ELSE 0 END) tue_12,
         SUM(CASE WHEN iwd + 80 = invoice_date THEN 1 ELSE 0 END) wed_12,
         SUM(CASE WHEN iwd + 81 = invoice_date THEN 1 ELSE 0 END) thu_12,
         SUM(CASE WHEN iwd + 82 = invoice_date THEN 1 ELSE 0 END) fri_12,
         SUM(CASE WHEN iwd + 83 = invoice_date THEN 1 ELSE 0 END) sat_12,
         SUM(CASE WHEN iwd + 84 = invoice_date THEN 1 ELSE 0 END) sun_13,
         SUM(CASE WHEN iwd + 85 = invoice_date THEN 1 ELSE 0 END) mon_13,
         SUM(CASE WHEN iwd + 86 = invoice_date THEN 1 ELSE 0 END) tue_13,
         SUM(CASE WHEN iwd + 87 = invoice_date THEN 1 ELSE 0 END) wed_13,
         SUM(CASE WHEN iwd + 88 = invoice_date THEN 1 ELSE 0 END) thu_13,
         SUM(CASE WHEN iwd + 89 = invoice_date THEN 1 ELSE 0 END) fri_13,
         SUM(CASE WHEN iwd + 90 = invoice_date THEN 1 ELSE 0 END) sat_13,
         SUM(CASE WHEN iwd + 91 = invoice_date THEN 1 ELSE 0 END) sun_14,
         SUM(CASE WHEN iwd + 92 = invoice_date THEN 1 ELSE 0 END) mon_14,
         SUM(CASE WHEN iwd + 93 = invoice_date THEN 1 ELSE 0 END) tue_14,
         SUM(CASE WHEN iwd + 94 = invoice_date THEN 1 ELSE 0 END) wed_14,
         SUM(CASE WHEN iwd + 95 = invoice_date THEN 1 ELSE 0 END) thu_14,
         SUM(CASE WHEN iwd + 96 = invoice_date THEN 1 ELSE 0 END) fri_14,
         SUM(CASE WHEN iwd + 97 = invoice_date THEN 1 ELSE 0 END) sat_14,
         SUM(CASE WHEN iwd + 98 = invoice_date THEN 1 ELSE 0 END) sun_15,
         SUM(CASE WHEN iwd + 99 = invoice_date THEN 1 ELSE 0 END) 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') - 7 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,
                 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

Open in new window


The users want this report. Now how will or which format do I give them . They want a csv file and columns for now we cay sun_1, mon_1 etc. But they are asking if we can give then the real date of the day. They will always put a sunday date for start_date. I will check if the date entered is sunday or not. But how to give column names as real date? Help appreciated.  Sample table scripts I am attaching here again.

WAD-EX0128      ALEXIAN BROTHERS MEDICAL CENTER      Red Blood Cells

SUN_1 has to be 04-jan-2015  and so on....

 sample-table-and-data.txt
LVL 6
anumosesAsked:
Who is Participating?
 
sdstuberCommented:
how about this?
If you always start on a Sunday and always run the report for 12 weeks, that some makes parts of it simpler.



  SELECT inv_product_type,
         customer_id,
         patient_name,
         data
    FROM (SELECT 0 ord,
                 'INV_PRODUCT_TYPE' inv_product_type,
                 'CUSTOMER_ID' customer_id,
                 'PATIENT_NAME' patient_name,
                 days data
            FROM (    SELECT SUBSTR(
                                 SYS_CONNECT_BY_PATH(
                                     TO_CHAR(NEXT_DAY(TO_DATE( :ym, 'YYYYMM') - 1, 'Sun') + LEVEL - 1, 'dd-Mon-yyyy'),
                                     ','),
                                 2)
                                 days
                        FROM DUAL
                  CONNECT BY LEVEL <= 12 * 7
                    ORDER BY LEVEL DESC)
           WHERE ROWNUM = 1
          UNION ALL
            SELECT 1 ord,
                   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)
                   || ','
                   || SUM(
                          CASE
                              WHEN iwd + 1 >= TRUNC(invoice_date, 'mm')
                              THEN
                                  CASE WHEN iwd + 1 = invoice_date THEN 1 ELSE 0 END
                          END)
                   || ','
                   || SUM(
                          CASE
                              WHEN iwd + 2 >= TRUNC(invoice_date, 'mm')
                              THEN
                                  CASE WHEN iwd + 2 = invoice_date THEN 1 ELSE 0 END
                          END)
                   || ','
                   || SUM(
                          CASE
                              WHEN iwd + 3 >= TRUNC(invoice_date, 'mm')
                              THEN
                                  CASE WHEN iwd + 3 = invoice_date THEN 1 ELSE 0 END
                          END)
                   || ','
                   || SUM(
                          CASE
                              WHEN iwd + 4 >= TRUNC(invoice_date, 'mm')
                              THEN
                                  CASE WHEN iwd + 4 = invoice_date THEN 1 ELSE 0 END
                          END)
                   || ','
                   || SUM(
                          CASE
                              WHEN iwd + 5 >= TRUNC(invoice_date, 'mm')
                              THEN
                                  CASE WHEN iwd + 5 = invoice_date THEN 1 ELSE 0 END
                          END)
                   || ','
                   || SUM(CASE WHEN iwd + 6 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 7 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 8 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 9 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 10 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 11 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 12 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 13 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 14 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 15 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 16 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 17 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 18 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 19 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 20 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 21 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 22 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 23 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 24 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 25 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 26 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 27 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 28 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 29 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 30 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 31 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 32 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 33 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 34 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 35 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 36 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 37 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 38 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 39 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 40 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 41 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 42 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 43 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 44 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 45 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 46 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 47 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 48 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 49 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 50 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 51 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 52 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 53 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 54 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 55 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 56 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 57 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 58 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 59 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 60 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 61 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 62 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 63 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 64 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 65 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 66 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 67 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 68 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 69 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 70 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 71 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 72 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 73 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 74 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 75 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 76 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 77 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 78 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 79 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 80 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 81 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 82 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 83 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 84 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 85 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 86 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 87 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 88 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 89 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 90 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 91 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 92 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 93 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 94 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 95 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 96 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 97 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 98 = invoice_date THEN 1 ELSE 0 END)
                   || ','
                   || SUM(CASE WHEN iwd + 99 = invoice_date THEN 1 ELSE 0 END)
              FROM (SELECT TO_CHAR(TRUNC(ih.invoice_date, 'mm'), 'yyyymm') ym,
                           NEXT_DAY(TO_DATE( :ym, 'YYYYMM') - 1, '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,
                           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 >= NEXT_DAY(TO_DATE( :ym, 'YYYYMM') - 1, 'Sun')
                       AND ih.invoice_date < NEXT_DAY(TO_DATE( :ym, 'YYYYMM') - 1, 'Sun') + 12 * 7
                       AND vp.inv_product_type IN ('RBC',
                                                   'LRBC',
                                                   'LPHER',
                                                   'PHER',
                                                   'FFP',
                                                   'FP24',
                                                   'CRYO'))
          GROUP BY inv_product_type, customer_id, patient_name)
ORDER BY ord,
         inv_product_type,
         customer_id,
         patient_name

Open in new window

0
 
sdstuberCommented:
Can you post the full  csv output  that is expected?
0
 
anumosesAuthor Commented:
I am attaching an excel spread sheet. Data is from 1st sunday of Jan 2015 1/4/2015 ( for 12 weeks)

May be when I posted the data for quantities may have been misplaced by wrong ones. Just to show the look of the report. Any mistake in giving the requirement, please let me know. testing-file.xls
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
anumosesAuthor Commented:
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,
         SUM(CASE WHEN iwd + 6 = invoice_date THEN 1 ELSE 0 END) sat_1,
         SUM(CASE WHEN iwd + 7 = invoice_date THEN 1 ELSE 0 END) sun_2,
         SUM(CASE WHEN iwd + 8 = invoice_date THEN 1 ELSE 0 END) mon_2,
         SUM(CASE WHEN iwd + 9 = invoice_date THEN 1 ELSE 0 END) tue_2,
         SUM(CASE WHEN iwd + 10 = invoice_date THEN 1 ELSE 0 END) wed_2,
         SUM(CASE WHEN iwd + 11 = invoice_date THEN 1 ELSE 0 END) thu_2,
         SUM(CASE WHEN iwd + 12 = invoice_date THEN 1 ELSE 0 END) fri_2,
         SUM(CASE WHEN iwd + 13 = invoice_date THEN 1 ELSE 0 END) sat_2,
         SUM(CASE WHEN iwd + 14 = invoice_date THEN 1 ELSE 0 END) sun_3,
         SUM(CASE WHEN iwd + 15 = invoice_date THEN 1 ELSE 0 END) mon_3,
         SUM(CASE WHEN iwd + 16 = invoice_date THEN 1 ELSE 0 END) tue_3,
         SUM(CASE WHEN iwd + 17 = invoice_date THEN 1 ELSE 0 END) wed_3,
         SUM(CASE WHEN iwd + 18 = invoice_date THEN 1 ELSE 0 END) thu_3,
         SUM(CASE WHEN iwd + 19 = invoice_date THEN 1 ELSE 0 END) fri_3,
         SUM(CASE WHEN iwd + 20 = invoice_date THEN 1 ELSE 0 END) sat_3,
         SUM(CASE WHEN iwd + 21 = invoice_date THEN 1 ELSE 0 END) sun_4,
         SUM(CASE WHEN iwd + 22 = invoice_date THEN 1 ELSE 0 END) mon_4,
         SUM(CASE WHEN iwd + 23 = invoice_date THEN 1 ELSE 0 END) tue_4,
         SUM(CASE WHEN iwd + 24 = invoice_date THEN 1 ELSE 0 END) wed_4,
         SUM(CASE WHEN iwd + 25 = invoice_date THEN 1 ELSE 0 END) thu_4,
         SUM(CASE WHEN iwd + 26 = invoice_date THEN 1 ELSE 0 END) fri_4,
         SUM(CASE WHEN iwd + 27 = invoice_date THEN 1 ELSE 0 END) sat_4,
         SUM(CASE WHEN iwd + 28 = invoice_date THEN 1 ELSE 0 END) sun_5,
         SUM(CASE WHEN iwd + 29 = invoice_date THEN 1 ELSE 0 END) mon_5,
         SUM(CASE WHEN iwd + 30 = invoice_date THEN 1 ELSE 0 END) tue_5,
         SUM(CASE WHEN iwd + 31 = invoice_date THEN 1 ELSE 0 END) wed_5,
         SUM(CASE WHEN iwd + 32 = invoice_date THEN 1 ELSE 0 END) thu_5,
         SUM(CASE WHEN iwd + 33 = invoice_date THEN 1 ELSE 0 END) fri_5,
         SUM(CASE WHEN iwd + 34 = invoice_date THEN 1 ELSE 0 END) sat_5,
         SUM(CASE WHEN iwd + 35 = invoice_date THEN 1 ELSE 0 END) sun_5,
         SUM(CASE WHEN iwd + 36 = invoice_date THEN 1 ELSE 0 END) mon_6,
         SUM(CASE WHEN iwd + 37 = invoice_date THEN 1 ELSE 0 END) tue_6,
         SUM(CASE WHEN iwd + 38 = invoice_date THEN 1 ELSE 0 END) wed_6,
         SUM(CASE WHEN iwd + 39 = invoice_date THEN 1 ELSE 0 END) thu_6,
         SUM(CASE WHEN iwd + 40 = invoice_date THEN 1 ELSE 0 END) fri_6,
         SUM(CASE WHEN iwd + 41 = invoice_date THEN 1 ELSE 0 END) sat_6,
         SUM(CASE WHEN iwd + 42 = invoice_date THEN 1 ELSE 0 END) sun_7,
         SUM(CASE WHEN iwd + 43 = invoice_date THEN 1 ELSE 0 END) mon_7,
         SUM(CASE WHEN iwd + 44 = invoice_date THEN 1 ELSE 0 END) tue_7,
         SUM(CASE WHEN iwd + 45 = invoice_date THEN 1 ELSE 0 END) wed_7,
         SUM(CASE WHEN iwd + 46 = invoice_date THEN 1 ELSE 0 END) thu_7,
         SUM(CASE WHEN iwd + 47 = invoice_date THEN 1 ELSE 0 END) fri_7,
         SUM(CASE WHEN iwd + 48 = invoice_date THEN 1 ELSE 0 END) sat_7,
         SUM(CASE WHEN iwd + 49 = invoice_date THEN 1 ELSE 0 END) sun_8,
         SUM(CASE WHEN iwd + 50 = invoice_date THEN 1 ELSE 0 END) mon_8,
         SUM(CASE WHEN iwd + 51 = invoice_date THEN 1 ELSE 0 END) tue_8,
         SUM(CASE WHEN iwd + 52 = invoice_date THEN 1 ELSE 0 END) wed_8,
         SUM(CASE WHEN iwd + 53 = invoice_date THEN 1 ELSE 0 END) thu_8,
         SUM(CASE WHEN iwd + 54 = invoice_date THEN 1 ELSE 0 END) fri_8,
         SUM(CASE WHEN iwd + 55 = invoice_date THEN 1 ELSE 0 END) sat_8,
         SUM(CASE WHEN iwd + 56 = invoice_date THEN 1 ELSE 0 END) sun_9,
         SUM(CASE WHEN iwd + 57 = invoice_date THEN 1 ELSE 0 END) mon_9,
         SUM(CASE WHEN iwd + 58 = invoice_date THEN 1 ELSE 0 END) tue_9,
         SUM(CASE WHEN iwd + 59 = invoice_date THEN 1 ELSE 0 END) wed_9,
         SUM(CASE WHEN iwd + 60 = invoice_date THEN 1 ELSE 0 END) thu_9,
         SUM(CASE WHEN iwd + 61 = invoice_date THEN 1 ELSE 0 END) fri_9,
         SUM(CASE WHEN iwd + 62 = invoice_date THEN 1 ELSE 0 END) sat_9,
         SUM(CASE WHEN iwd + 63 = invoice_date THEN 1 ELSE 0 END) sun_10,
         SUM(CASE WHEN iwd + 64 = invoice_date THEN 1 ELSE 0 END) mon_10,
         SUM(CASE WHEN iwd + 65 = invoice_date THEN 1 ELSE 0 END) tue_10,
         SUM(CASE WHEN iwd + 66 = invoice_date THEN 1 ELSE 0 END) wed_10,
         SUM(CASE WHEN iwd + 67 = invoice_date THEN 1 ELSE 0 END) thu_10,
         SUM(CASE WHEN iwd + 68 = invoice_date THEN 1 ELSE 0 END) fri_10,
         SUM(CASE WHEN iwd + 69 = invoice_date THEN 1 ELSE 0 END) sat_10,
         SUM(CASE WHEN iwd + 70 = invoice_date THEN 1 ELSE 0 END) sun_11,
         SUM(CASE WHEN iwd + 71 = invoice_date THEN 1 ELSE 0 END) mon_11,
         SUM(CASE WHEN iwd + 72 = invoice_date THEN 1 ELSE 0 END) tue_11,
         SUM(CASE WHEN iwd + 73 = invoice_date THEN 1 ELSE 0 END) wed_11,
         SUM(CASE WHEN iwd + 74 = invoice_date THEN 1 ELSE 0 END) thu_11,
         SUM(CASE WHEN iwd + 75 = invoice_date THEN 1 ELSE 0 END) fri_11,
         SUM(CASE WHEN iwd + 76 = invoice_date THEN 1 ELSE 0 END) sat_11,
         SUM(CASE WHEN iwd + 77 = invoice_date THEN 1 ELSE 0 END) sun_12,
         SUM(CASE WHEN iwd + 78 = invoice_date THEN 1 ELSE 0 END) mon_12,
         SUM(CASE WHEN iwd + 79 = invoice_date THEN 1 ELSE 0 END) tue_12,
         SUM(CASE WHEN iwd + 80 = invoice_date THEN 1 ELSE 0 END) wed_12,
         SUM(CASE WHEN iwd + 81 = invoice_date THEN 1 ELSE 0 END) thu_12,
         SUM(CASE WHEN iwd + 82 = invoice_date THEN 1 ELSE 0 END) fri_12,
         SUM(CASE WHEN iwd + 83 = invoice_date THEN 1 ELSE 0 END) sat_12,
         SUM(CASE WHEN iwd + 84 = invoice_date THEN 1 ELSE 0 END) sun_13,
         SUM(CASE WHEN iwd + 85 = invoice_date THEN 1 ELSE 0 END) mon_13,
         SUM(CASE WHEN iwd + 86 = invoice_date THEN 1 ELSE 0 END) tue_13,
         SUM(CASE WHEN iwd + 87 = invoice_date THEN 1 ELSE 0 END) wed_13,
         SUM(CASE WHEN iwd + 88 = invoice_date THEN 1 ELSE 0 END) thu_13,
         SUM(CASE WHEN iwd + 89 = invoice_date THEN 1 ELSE 0 END) fri_13,
         SUM(CASE WHEN iwd + 90 = invoice_date THEN 1 ELSE 0 END) sat_13,
         SUM(CASE WHEN iwd + 91 = invoice_date THEN 1 ELSE 0 END) sun_14,
         SUM(CASE WHEN iwd + 92 = invoice_date THEN 1 ELSE 0 END) mon_14,
         SUM(CASE WHEN iwd + 93 = invoice_date THEN 1 ELSE 0 END) tue_14,
         SUM(CASE WHEN iwd + 94 = invoice_date THEN 1 ELSE 0 END) wed_14,
         SUM(CASE WHEN iwd + 95 = invoice_date THEN 1 ELSE 0 END) thu_14,
         SUM(CASE WHEN iwd + 96 = invoice_date THEN 1 ELSE 0 END) fri_14,
         SUM(CASE WHEN iwd + 97 = invoice_date THEN 1 ELSE 0 END) sat_14,
         SUM(CASE WHEN iwd + 98 = invoice_date THEN 1 ELSE 0 END) sun_15,
         SUM(CASE WHEN iwd + 99 = invoice_date THEN 1 ELSE 0 END) 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,
                 patient_name,
                 ih.invoice_date
            FROM invoice_header ih,
                 invoice_detail id,
                 item_profile ip,
                 valid_products_lab 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

I kind of got this query to run from 1st sunday of Jan when they put 04jan-2015'
but cannot get to show dates as column names.

Open in new window

0
 
MikeOM_DBACommented:
. . .
CASE
  WHEN Iwd = TRUNC ( Invoice_Date, 'mm' )
  THEN
    CASE WHEN Iwd = Invoice_Date THEN 1 ELSE 0 END
END
. . .
Please explain how can "Iwd" be a month and at the same time a date?
The query you posted does not work at all, I suspect you want us to do your work.
0
 
anumosesAuthor Commented:
Perfect. This is what I am looking for. I am sorry the user keeps changing their minds. They wanted output in excel and I have written using UTL_FILE.FOPEN. The output is in excel. Only problem is that I have days hard coded as before. But your solution really helped to for csv. I will close this and open a new question for adding dates in excel format in my routine I have written. I tried to add your dates concept. But got error. So will be posting it soon for help.
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.