Oracle query
https://www.experts-exchange.com/questions/28663209/Oracle-9i-ORA-00904-CONNECT-BY-ISLEAF-invalid-identifier-Other-alternatives.html
This is closed. A correct solution given and helped by @sdstuber. Thanks for his oracle knowledge.
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
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_nameThe 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
Can you post the full csv output that is expected?
ASKER
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
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
ASKER
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.
. . .Please explain how can "Iwd" be a month and at the same time a date?
CASE
WHEN Iwd = TRUNC ( Invoice_Date, 'mm' )
THEN
CASE WHEN Iwd = Invoice_Date THEN 1 ELSE 0 END
END
. . .
The query you posted does not work at all, I suspect you want us to do your work.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.