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_id

Open in new window


start_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,'Mon')=to_char(add_months(to_date(:end_date,'DD-MON-YYYY'),-16),'Mon') then id.order_qty else 0 end)  dec_2013,
LVL 6
anumosesAsked:
Who is Participating?
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:
first

don't write a date condition like this..

and   trunc(ih.invoice_date) between  :start_date and :end_date

instead write it like this...

and   (ih.invoice_date >=  :start_date and  ih.invoice_date < :end_date + 1)

this assumes :Start_date and :end_date are actually dates, not strings
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
sdstuberCommented:
for your math.


-16 doesn't seem correct for dec-2013

if your end date is 31-dec-2014,  then,  you should subtract 12,  not 16
0
sdstuberCommented:
and,  since your inputs appear to be strings,  not actually dates,  then

and   (ih.invoice_date >=  :start_date and  ih.invoice_date < :end_date + 1)

should actually be this...


and   (ih.invoice_date >=  to_date(:start_date,'DD-MON-YYYY') and  ih.invoice_date < to_date(:end_date,'DD-MON-YYYY') + 1)
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.

sdstuberCommented:
also....

since your data is actually DATES,  then don't convert it to a string in order to do your comparisons

not only is it inefficient,  but it can also lead to mistakes, like your usage which would combine all January together for all years since you were only comparing the month name, not looking at the month and year

try something like this instead for your CASE conditions

case when trunc(ih.invoice_date,'mm')=add_months(to_date(:end_date,'DD-MON-YYYY'),-14) then id.order_qty else 0 end



and, if you're wondering why the trunc() in the case is ok here but not above in my first response,  it's because the one in the first response was for the WHERE clause to filter data in your tables or indexes.  The condition in the case is for data where you must look at every row returned from the where clause
0
slightwv (䄆 Netminder) Commented:
Since you will also be spanning more than a year, I think this is wrong:
to_char(ih.invoice_date,'Mon')

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,'MonYY')=to_char(add_months(to_date(:end_date,'DD-MON-YYYY'),-15),'MonYY') then id.order_qty else 0 end)  jan_2014,
0
slightwv (䄆 Netminder) Commented:
anumoses,
I missed that sdstuber had already caught the issue I posted.  He just solved it a different way.  Please ignore my post.
0
Walter RitzelSenior Software EngineerCommented:
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
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))

Open in new window

0
anumosesAuthor Commented:
I am on 9i
0
Walter RitzelSenior Software EngineerCommented:
0
slightwv (䄆 Netminder) Commented:
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.
0
sdstuberCommented:
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
0
awking00Commented:
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,'mm'),'mon_yyyy') as month,sum(id.qty) as sum_qty
  2  from ih, id
  3  where ih.inv_num = id.inv_num
  4  and ih.inv_dt >= to_date('20140601','yyyymmdd')
  5  and ih.inv_dt < to_date('20150301','yyyymmdd')
  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.
0
Walter RitzelSenior Software EngineerCommented:
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.
0
anumosesAuthor Commented:
thanks
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.

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.