oracle dates question
Select distinct
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'
END
inv_product_type,
ih.customer_id,patient_name,hs.name,
sum(case when to_char(ih.invoice_date,'Mon')=to_char(add_months(to_date(:end_date,'DD-MON-YYYY'),-15),'Mon') then id.order_qty else 0 end) jan_2014,
sum(case when to_char(ih.invoice_date,'Mon')=to_char(add_months(to_date(:end_date,'DD-MON-YYYY'),-14),'Mon') then id.order_qty else 0 end) feb_2014,
sum(case when to_char(ih.invoice_date,'Mon')=to_char(add_months(to_date(:end_date,'DD-MON-YYYY'),-13),'Mon') then id.order_qty else 0 end) mar_2014,
sum(case when to_char(ih.invoice_date,'Mon')=to_char(add_months(to_date(:end_date,'DD-MON-YYYY'),-12),'Mon') then id.order_qty else 0 end) apr_2014,
sum(case when to_char(ih.invoice_date,'Mon')=to_char(add_months(to_date(:end_date,'DD-MON-YYYY'),-11),'Mon') then id.order_qty else 0 end) may_2014,
sum(case when to_char(ih.invoice_date,'Mon')=to_char(add_months(to_date(:end_date,'DD-MON-YYYY'),-10),'Mon') then id.order_qty else 0 end) jun_2014,
sum(case when to_char(ih.invoice_date,'Mon')=to_char(add_months(to_date(:end_date,'DD-MON-YYYY'),-9),'Mon') then id.order_qty else 0 end) jul_2014,
sum(case when to_char(ih.invoice_date,'Mon')=to_char(add_months(to_date(:end_date,'DD-MON-YYYY'),-8),'Mon') then id.order_qty else 0 end) aug_2014,
sum(case when to_char(ih.invoice_date,'Mon')=to_char(add_months(to_date(:end_date,'DD-MON-YYYY'),-7),'Mon') then id.order_qty else 0 end) sep_2014,
sum(case when to_char(ih.invoice_date,'Mon')=to_char(add_months(to_date(:end_date,'DD-MON-YYYY'),-6),'Mon') then id.order_qty else 0 end) oct_2014,
sum(case when to_char(ih.invoice_date,'Mon')=to_char(add_months(to_date(:end_date,'DD-MON-YYYY'),-5),'Mon') then id.order_qty else 0 end) nov_2014,
sum(case when to_char(ih.invoice_date,'Mon')=to_char(add_months(to_date(:end_date,'DD-MON-YYYY'),-4),'Mon') then id.order_qty else 0 end) dec_2014
from invoice_header ih,
invoice_detail id,
item_profile ip,
customers@plab.world c,
valid_products@plab.world vp
where id.invoice_number = ih.invoice_number
and id.item_id = ip.item_id
and ip.item_type in ('P')
and ih.customer_id = c.customer_id
and trunc(ih.invoice_date) between :start_date and :end_date
and id.item_id = vp.product_code
and c.customer_id not in ('WAD-RD0018','WAD-RD0186')
AND vp.inv_product_type IN ('RBC',
'LRBC',
'LPHER',
'PHER')
group by inv_product_type,ih.customer_id,patient_name
order by inv_product_type,ih.customer_idstart_date is '01-jan-2014' and end_date is '31-dec-2014'. Now users want for 24 months. Is my date conversion correct? or is there a better way to do? from 01-jan-2013 to 31-dec-2014.
for dec_2013 I did
sum(case when to_char(ih.invoice_date,'M
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
anumoses,
I missed that sdstuber had already caught the issue I posted. He just solved it a different way. Please ignore my post.
I missed that sdstuber had already caught the issue I posted. He just solved it a different way. Please ignore my post.
If you use pivot table, you can achieve it. The problem with the pivot approach is that the columns would need to be hard coded (which you are doing anyway). The alternative for a completely dynamic solution, you would need to use pivot xml, but the end result will be xml and needs to be parsed.
I build a small sample of both queries below. If you need more information, go to Oracle docs:
http://www.oracle.com/technetwork/articles/sql/11g-pivot-097235.html
I build a small sample of both queries below. If you need more information, go to Oracle docs:
http://www.oracle.com/technetwork/articles/sql/11g-pivot-097235.html
CREATE TABLE tst_wrpc
(
inv_date date
, type varchar2(10)
, qty int
);
insert into tst_wrpc values(SYSDATE,'A',1);
insert into tst_wrpc values(SYSDATE+35,'A',1);
insert into tst_wrpc values(SYSDATE+70,'A',1);
insert into tst_wrpc values(SYSDATE+105,'A',1);
insert into tst_wrpc values(SYSDATE,'B',1);
insert into tst_wrpc values(SYSDATE+35,'B',2);
insert into tst_wrpc values(SYSDATE+70,'B',1);
insert into tst_wrpc values(SYSDATE+105,'B',2);
insert into tst_wrpc values(SYSDATE,'C',6);
insert into tst_wrpc values(SYSDATE+35,'C',1);
insert into tst_wrpc values(SYSDATE+70,'C',2);
insert into tst_wrpc values(SYSDATE+105,'C',3);
commit;
-- pivot example
select type, DT_XML from (SELECT TYPE
, TO_CHAR(inv_date, 'mm/yyyy') AS dt
, qty
FROM tst_wrpc)
pivot (SUM(qty) as tot_qty
FOR dt
IN ('05/2015','06/2015','07/2015')) -- here you will need to hardcode as possible values
-- pivot xml example
select type, DT_XML from (SELECT TYPE
, TO_CHAR(inv_date, 'mm/yyyy') AS dt
, qty
FROM tst_wrpc)
pivot xml(SUM(qty) as tot_qty
FOR dt
IN (select distinct to_char(inv_date,'mm/yyyy') as dt from tst_wrpc))ASKER
I am on 9i
Humm, so this will not work. You can try the xmlagg in 9i.
http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions215.htm
http://www.dba-oracle.com/t_converting_rows_columns.htm
http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions215.htm
http://www.dba-oracle.com/t_converting_rows_columns.htm
Walter,
anumoses doesn't want a delimited list. They want individual columns, then the addition of them by week and a grand total.
I don't see how XMLAGG will help with this. If you can post a working example, I would love to take a look at it.
anumoses doesn't want a delimited list. They want individual columns, then the addition of them by week and a grand total.
I don't see how XMLAGG will help with this. If you can post a working example, I would love to take a look at it.
no - she already has the pivot done, doing aggregation via xml could work, but it would be doing the same work she already has, except for more work building xml and the extracting it again
anumoses, You seem to struggle with date math and comparisons and I believe it's primarly because you're storing dates as characters and not as date datatypes. I would implore you to convert all of your character dates to date type, which will allow you to use the many date functions (e.g. add_months, trunk, months_between, etc.) you seem to need.
Look at the following query results based on dates:
SQL> select ih.inv_num, ih.inv_dt, id.qty
2 from ih, id
3 where ih.inv_num = id.inv_num;
INV_NUM INV_DT QTY
---------- --------- ----------
1 19-APR-15 3
2 02-APR-15 6
3 16-MAR-15 9
4 27-FEB-15 12
5 10-FEB-15 15
6 24-JAN-15 18
7 07-JAN-15 21
8 21-DEC-14 24
9 04-DEC-14 27
10 17-NOV-14 30
11 31-OCT-14 33
12 14-OCT-14 36
13 27-SEP-14 39
14 10-SEP-14 42
15 24-AUG-14 45
16 07-AUG-14 48
17 21-JUL-14 51
18 04-JUL-14 54
19 17-JUN-14 57
20 31-MAY-14 60
Assuming you just want the qty summed for the nine month period beginning 01-JUN-14, you could do so easily with the following query without having to convert string to dates and back again or any case statements: Note - I added the to_char function to represent the month the way your query showed it.
SQL> select to_char(trunc(ih.inv_dt,'m
2 from ih, id
3 where ih.inv_num = id.inv_num
4 and ih.inv_dt >= to_date('20140601','yyyymm
5 and ih.inv_dt < to_date('20150301','yyyymm
6 group by trunc(ih.inv_dt,'mm')
7 order by trunc(ih.inv_dt,'mm');
MONTH SUM_QTY
-------------- ----------
jun_2014 57
jul_2014 105
aug_2014 93
sep_2014 81
oct_2014 69
nov_2014 30
dec_2014 51
jan_2015 39
feb_2015 27
Hope this helps.
Look at the following query results based on dates:
SQL> select ih.inv_num, ih.inv_dt, id.qty
2 from ih, id
3 where ih.inv_num = id.inv_num;
INV_NUM INV_DT QTY
---------- --------- ----------
1 19-APR-15 3
2 02-APR-15 6
3 16-MAR-15 9
4 27-FEB-15 12
5 10-FEB-15 15
6 24-JAN-15 18
7 07-JAN-15 21
8 21-DEC-14 24
9 04-DEC-14 27
10 17-NOV-14 30
11 31-OCT-14 33
12 14-OCT-14 36
13 27-SEP-14 39
14 10-SEP-14 42
15 24-AUG-14 45
16 07-AUG-14 48
17 21-JUL-14 51
18 04-JUL-14 54
19 17-JUN-14 57
20 31-MAY-14 60
Assuming you just want the qty summed for the nine month period beginning 01-JUN-14, you could do so easily with the following query without having to convert string to dates and back again or any case statements: Note - I added the to_char function to represent the month the way your query showed it.
SQL> select to_char(trunc(ih.inv_dt,'m
2 from ih, id
3 where ih.inv_num = id.inv_num
4 and ih.inv_dt >= to_date('20140601','yyyymm
5 and ih.inv_dt < to_date('20150301','yyyymm
6 group by trunc(ih.inv_dt,'mm')
7 order by trunc(ih.inv_dt,'mm');
MONTH SUM_QTY
-------------- ----------
jun_2014 57
jul_2014 105
aug_2014 93
sep_2014 81
oct_2014 69
nov_2014 30
dec_2014 51
jan_2015 39
feb_2015 27
Hope this helps.
For 9i, I dont have how to test, but for 11g, it will work, but as well stated by sdstuber, it will require to deal with XML parsing after. I think is a matter of weight in what will cost less in terms of future changes: change the query each time they want to change the number of months to view or create now a routine to parse the xml where the number of columns will not be relevant.
ASKER
thanks
to_char(ih.invoice_date,'M
Without the year in the conversion there is no way to differentiate between Jan-2013 and Jan-2014.
I believe you'll need to add the year to both sides:
case when to_char(ih.invoice_date,'M