 getting years [message #426230] |
Wed, 14 October 2009 08:52  |
harshas
Messages: 1
Registered: October 2009
Location: Hyderabad
|
Junior Member |
|
|
hi , here is my problem
i want to get sum of quantity in previous_year,current_year,previous_month,previous_month,previous_week,current_week
if P_date=01-jan-2009 then how to get previous month ie,dec-2008,
also week 31-dec-2008............
here is my loop
how can we manage
this my cursor
cursor oe_cur(c_previous_year date, c_current_year date)
IS
SELECT sum(ordered_quantity) Total_sum
,creation_date
,sold_to_org_id
,inventory_item_id
FROM oe_order_lines_all
WHERE creation_date between c_previous_year and c_current_year
and sold_to_org_id= p_cust_id
group by creation_date
,sold_to_org_id
,inventory_item_id;
--here begin
begin
select to_char(p_date, 'yyyy')
into y from dual;
select to_char('01-JAN-'||(y-1))
INTO v_previous_y from dual;
select to_char('31-DEC-'||y)
INTO v_current_y from dual;
for c_rec in oe_cur(v_previous_y,v_current_y)
loop
v_system_year := to_char(c_rec.creation_date,'yyyy') ;
v_system_month := to_char(c_rec.creation_date,'mm');
v_system_week := to_char(c_rec.creation_date,'w');
v_current_year := to_char(p_date,'yyyy');
v_current_month := to_char(p_date,'mm');
v_current_week := to_char(p_date,'w');
v_previous_year := (v_current_year)-1;
v_previous_month := (v_current_month)-1;
v_previous_week := (v_current_week)-1;
--dbms_output.put_line ('previous_year:'||v_previous_year);
if (v_system_year = v_current_year) then
v_current_year_qty := (c_rec.Total_sum)+ v_current_year_qty;
if (v_system_month = 1) then
v_system_month1 := 12;
v_current_month_qty := (c_rec.Total_sum) + v_current_month_qty ;
if (v_system_month = v_current_month) then
v_current_month_qty := (c_rec.Total_sum) + v_current_month_qty ;
if (v_system_week = v_current_week ) then
v_current_week_qty :=(c_rec.total_sum)+v_current_week_qty;
end if;
if(v_system_week = v_previous_week ) then
v_previous_week_qty :=(c_rec.total_sum)+v_previous_week_qty;
end if;
end if;
if(v_system_month = v_previous_month) then
v_previous_month_qty := (c_rec.Total_sum)+ v_previous_month_qty ;
end if;
end if;
if (v_system_year = v_previous_year) then
v_previous_year_qty := (c_rec.Total_sum)+ v_previous_year_qty;
end if;
end loop;
select customer_name
into v_customer_name
from ra_customers rc
where customer_id =p_cust_id ;
dbms_output.put_line ('Customer Name: ' || v_customer_name ||' '||
--'Item: ' || v_segment1||' '||
' Last Year qty: '|| v_previous_year_qty ||' '||
' current Year qty : '||v_current_year_qty ||' '||
' Previous Month qty: '||v_previous_month_qty || ' '||
' Curreent Month qty: '||v_current_month_qty || ' '||
' Last week : '|| v_previous_week_qty|| ' '||
' Current Week:' || v_current_week_qty);
|
|
|
|
| Re: getting years [message #426234 is a reply to message #426230] |
Wed, 14 October 2009 09:09  |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
1) When posting code can you please use code tags, it makes it a lot easier to read - see the orafaq forum guide if you're not sure how.
2) I'm not sure what your exact problem is. You have code that generates totals, does it not do what you want?
You need to be clearer on what the issue is what output you want in the end.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
