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?

[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.

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
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
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

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:
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
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.