Home » SQL & PL/SQL » SQL & PL/SQL » Group by - date
Group by - date [message #232624] Sun, 22 April 2007 12:35 Go to next message
milford_f
Messages: 18
Registered: July 2006
Junior Member
Hi

I am trying to pick data from 2 tables and summarise it into one. Thanks in advance

table 1
Order product Order_date order_qty value
xyz 111 10-MAY-2007 10 1000
xyz 222 10-MAY-2007 20 3000

table 2
Order product delivery_date qty
xyz 111 30-MAY-2007 7
xyz 111 10-JUN-2007 3
xyz 222 30-MAY-2007 20

What I am trying to achieve is
Order product order_date Delivery_date qty value
xyz 111 10-MAY-2007 30-MAY-2007 7 700
xyz 111 10-MAY-2007 10-JUNE-2007 3 300
xyz 222 10-MAY-2007 30-MAY-2007 20 3000

Cheers
Milford.
Re: Group by - date [message #232625 is a reply to message #232624] Sun, 22 April 2007 12:38 Go to previous messageGo to next message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
can you show us what you tried so far?
Re: Group by - date [message #232626 is a reply to message #232624] Sun, 22 April 2007 12:39 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
Please read & follow the #1 & #2 STICKY posts at the top of this forum.
Re: Group by - date [message #232627 is a reply to message #232624] Sun, 22 April 2007 12:42 Go to previous messageGo to next message
milford_f
Messages: 18
Registered: July 2006
Junior Member
attached the sql script
  • Attachment: lft.sql
    (Size: 4.33KB, Downloaded 162 times)
Re: Group by - date [message #232630 is a reply to message #232627] Sun, 22 April 2007 13:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What did you post here? Did you post the right file?
I don't see any of your data.
I don't see a query for your requirement with your result.

Regards
Michel
Re: Group by - date [message #232633 is a reply to message #232624] Sun, 22 April 2007 13:29 Go to previous messageGo to next message
milford_f
Messages: 18
Registered: July 2006
Junior Member
here is my script

Insert into ord_sale_fcast_tmp (s_region, s_related_customer, s_cust_ref, s_ord_doc_ref, s_ord_tracer, s_product, s_required_date, s_line_del_date, s_customer_currency, s_Ordered_Qty, s_Order_value, s_invoiced_qty)
select oh_area, oh_related_cust, oh_cust_ref, oh_doc_ref, oh_num, ol_item_code, to_date(oh_cust_date,'DD-MON-RR'), null, oh_crcy_code, ol_qty, Decode(NVL(ol_qty,0),0,0,(ol_frgn_unit_s+ol_frgn_charge)*ol_qty), ol_inv_qty
from snsaleorders, snsaleorderlines
where oh_num=ol_oh_num
and oh_stat='O'
and substr(oh_doc_ref,1,2) in ('SV','TD','BD');
commit;

------------------------------------------------------------
-- INSERT forecast DATA INTO ORD_SALE_FCAST_TMP
------------------------------------------------------------

Insert into ord_sale_fcast_tmp (s_region, s_related_customer, s_cust_ref, s_ord_doc_ref, s_ord_tracer, s_product, s_required_date, s_line_del_date, s_Outstanding_qty)
select region, related_customer, cust_ref, ord_doc_ref, ord_tracer, product, to_date(required_date,'DD-MON-RR'), to_date(line_delivery_date,'DD-MON-RR'), NVL(P1_qty,0)+NVL(P2_qty,0)+NVL(P3_qty,0)+NVL(P4_qty,0)+NVL(P5_qty,0)+NVL(P6_qty,0)+NVL(P7_qty,0)+NVL(P8_qty,0)+NVL(P9_qty,0)+NVL(P10 _qty,0)+NVL(P11_qty,0)+NVL(P12_qty,0)
from snforecast
where oh_stat='O';

commit;
-------------------------------------------------------------
--SUMMARY
-------------------------------------------------------------

insert into ord_sale_fcast
select s_region, s_related_customer, s_cust_ref, s_ord_doc_ref, s_ord_tracer, s_product, s_required_date, s_line_del_date, s_customer_currency, SUM(s_Ordered_Qty), SUM(s_Order_value), SUM(s_Invoiced_qty), SUM(s_outstanding_qty)
from ord_sale_fcast_tmp
group by s_region, s_related_customer, s_cust_ref, s_ord_doc_ref, s_ord_tracer, s_product, s_required_date, s_line_del_date, s_customer_currency;

commit;

output is

REGION RELATED_CUSTOMER CUST_REF ORD_DOC_REF ORD_TRACER PRODUCT REQUIRED_DATE LINE_DEL_DATE CUSTOMER_ ORDERED_QTY ORDER_VALUE INVOICED_QTY OUTSTANDING_QTY
508 508100 EXCHANGE - 590&488 BD0008751 27400 66000840 17-Feb-07 19-Apr-07 110
508 508100 EXCHANGE - 590&488 BD0008751 27400 66000851 17-Feb-07 19-Apr-07 9
508 508100 EXCHANGE - 590&488 BD0008751 27400 66000851 17-Feb-07 USD 9 0
508 508100 EXCHANGE - 590&488 BD0008751 27400 66007328 17-Feb-07 USD 42 3735.48 42
508 508100 EXCHANGE - 590&488 BD0008751 27400 66000840 17-Feb-07 USD 110 9868.1
508 508100 EXCHANGE - 590&488 BD0008751 27400 66000792 17-Feb-07 USD 20 3736.4 20
508 508100 EXCHANGE - 590&488 BD0008751 27400 66000841 17-Feb-07 USD 60 5030.4 60

i hope this helps.

Thanks again
Re: Group by - date [message #232635 is a reply to message #232633] Sun, 22 April 2007 13:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First read How to format your posts.

Then I can't read lines longer than 80 characters.

Finally, insert statements never produce an output.

Regards
Michel
Re: Group by - date [message #232675 is a reply to message #232635] Sun, 22 April 2007 23:43 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
please try this. i have craeted two tables t1 and t2 \

data is as :-

select * from t1
xxx,111,5/10/2007,10,1000
xxx,222,5/10/2007,20,3000



select * from t2
xxx,111,5/30/2007,7
xxx,111,6/10/2007,3
xxx,222,5/30/2007,20


select t1.order1,t1.product,to_char(t1.ORDER_DATE,'dd-MON-YYYY'),to_char(t2.DELIVERY_DATE,'dd-mon-YYYY'),t2.QTY,t1.VALUE from t1,t2 where t1.ORDER1=t2.ORDER1 and t1.PRODUCT=t2.PRODUCT

xxx,111,10-MAY-2007,30-may-2007,7,1000
xxx,111,10-MAY-2007,10-jun-2007,3,1000
xxx,222,10-MAY-2007,30-may-2007,20,3000


but i am not getting how you are getting value field 700,300,3000...

--Yash
Re: Group by - date [message #232678 is a reply to message #232675] Mon, 23 April 2007 00:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I begin to help when:
1/ you'll post create table and insert statements to build your exemple
2/ you'll find the "enter" key to split your line. Do you know that a statement can spread over more than one line?

Regards
Michel
Re: Group by - date [message #232749 is a reply to message #232678] Mon, 23 April 2007 03:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
@Michael - relax. The whole INSERT/CREATE TABLE thing really isn't worth getting worked up over. It's nice when they do provide them, but it's pretty rare all things considered.

General Statement - I think some of the regular posters here are getting a little bit too demanding of the posters here. People come here and post problems that they have - if we make too many demands on them, and/or don't provide answers they'll just go elsewhere and OraFaq will end up as a quiet little backwater with no-one posting to it.

CREATE TABLE table_1(ORD VARCHAR2(3),   product NUMBER(3),   order_date DATE,   order_qty NUMBER(3),   VALUE NUMBER);

CREATE TABLE table_2(ORD VARCHAR2(3),   product number(3),   delivery_date date,   qty number);

insert into table_1 values ('xyz', 111, to_Date('10-MAY-2007','dd-mon-yyyy'), 10, 1000);
insert into table_1 values ('xyz', 222, to_Date('10-MAY-2007','dd-mon-yyyy'), 20, 3000);

insert into table_2 values ('xyz', 111, to_Date('30-MAY-2007','dd-mon-yyyy'), 7);
insert into table_2 values ('xyz', 111, to_Date('10-JUN-2007','dd-mon-yyyy'),  3);
insert into table_2 values ('xyz', 222, to_Date('30-MAY-2007','dd-mon-yyyy'),  20);


select t1.ord
      ,t1.product
      ,t1.order_date
      ,t2.delivery_Date
      ,t2.qty
      ,(t2.qty/t1.order_qty)*t1.value
from   table_1 t1 ,table_2 t2
where  t1.product = t2.product
and    t1.ord   = t2.ord;

ORD    PRODUCT ORDER_DAT DELIVERY_        QTY (T2.QTY/T1.ORDER_QTY)*T1.VALUE
--- ---------- --------- --------- ---------- ------------------------------
xyz        111 10-MAY-07 30-MAY-07          7                            700
xyz        111 10-MAY-07 10-JUN-07          3                            300
xyz        222 10-MAY-07 30-MAY-07         20                           3000
Re: Group by - date [message #232756 is a reply to message #232624] Mon, 23 April 2007 03:36 Go to previous messageGo to next message
milford_f
Messages: 18
Registered: July 2006
Junior Member
Sorry Guys,

I am pretty new to this whole thing......i will try to provide the info you need, as you need it.

Cheers,
Milford.
Re: Group by - date [message #232786 is a reply to message #232749] Mon, 23 April 2007 04:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
JRowbottom, I don't think it is too much to ask for create table and insert statements.
It just means helping others to help you.
This is asked in almost all forums I write.
Try to post such a question on AskTom and the answer you'll get from Tom is:
Quote:
no creates
no inserts
no look

Regards
Michel
Re: Group by - date [message #232807 is a reply to message #232786] Mon, 23 April 2007 06:04 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Michael, I quite agree with you - it would be only polite for people to do some work up front to save us the time, but I came to the conclusion over 1000 posts ago that most of the people posting questions here see things very differently, and that if I was going to answer questions and get some decent SQL practice in (and I now have a deeply ingrained habit of writing test cases) I'd just have to write most of them myself.

Even Tom Kyte (may his databases never need recovery) has to keep mentioning it.

Re: Group by - date [message #232808 is a reply to message #232807] Mon, 23 April 2007 06:32 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
JRowbottom, I think you're right. I agree we (as in me too) made it too much of a habit searching for missing info instead of trying to find an answer.
The exception may be the extremely lazy posters who very clearly did not make any effort but starting up their browsers, but in general I will try and be more helpful from now on.
Please keep it up as our 'conscience' Wink
Re: Group by - date [message #232817 is a reply to message #232749] Mon, 23 April 2007 07:09 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
JRowbottom wrote on Mon, 23 April 2007 04:06


General Statement - I think some of the regular posters here are getting a little bit too demanding of the posters here. People come here and post problems that they have - if we make too many demands on them, and/or don't provide answers they'll just go elsewhere and OraFaq will end up as a quiet little backwater with no-one posting to it.



I'm guilty. I'm on new drugs now to help me with this now. Let's see how they work.
Previous Topic: How to print my input in excel through pl/sql procedure
Next Topic: DBMS_JOB Job not starting.
Goto Forum:
  


Current Time: Tue Dec 06 10:35:16 CST 2016

Total time taken to generate the page: 0.07317 seconds