Home » SQL & PL/SQL » SQL & PL/SQL » need the out put based on max date
need the out put based on max date [message #633896] Fri, 27 February 2015 03:24 Go to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Hi all,

create table xxc_transctions(INVENTORY_ITEM_ID number,LAST_INVOICED_DATE date,  COST_AMOUNT  number,SALES_AMOUNT number);

insert into xxc_transctions(1234,to_date('7/14/2014'),  -410, -1086.5);
insert into xxc_transctions(1234,to_date('6/23/2014'),  -1335,-3537.75);
insert into xxc_transctions(1234,to_date('5/14/2014'),  0, 0);
insert into xxc_transctions(1234,to_date('5/23/2014'),  0,0);


INVENTORY_ITEM_ID	LAST_INVOICED_DATE	COST_AMOUNT	SALES_AMOUNT
      1234	            7/14/2014	            -410	-1086.5
      1234	            6/23/2014	            -1335	-3537.75
      1234	            7/14/2014	               0	  0
      1234	            6/23/2014	               0	  0
                                                 --------------- --------------
		                                    -1745	  -4624.25

I need the below output

INVENTORY_ITEM_ID	LAST_INVOICED_DATE	COST_AMOUNT	SALES_AMOUNT
      1234	            7/14/2014	            -1745	-4624.25




Thanks
Re: need the out put based on max date [message #633897 is a reply to message #633896] Fri, 27 February 2015 03:25 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Looks like you want to max the date and sum the amounts. What's the problem?
Re: need the out put based on max date [message #633898 is a reply to message #633896] Fri, 27 February 2015 03:26 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
So, what query did you manage to write in order to solve the problem?

[Updated on: Fri, 27 February 2015 03:26]

Report message to a moderator

Re: need the out put based on max date [message #633900 is a reply to message #633897] Fri, 27 February 2015 03:46 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Quote:
Looks like you want to max the date and sum the amounts. What's the problem?

Hi cookiemonster, after used max date , got the result as shown in the 1st post
Re: need the out put based on max date [message #633901 is a reply to message #633900] Fri, 27 February 2015 03:48 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Does that mean your problem is solved?
Re: need the out put based on max date [message #633902 is a reply to message #633901] Fri, 27 February 2015 03:53 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Quote:
Does that mean your problem is solved?

No not solved. I applied Max fun but no use, how to do this can you please help me?

SALES_AMOUNT is based on months like Jan, feb ...
Re: need the out put based on max date [message #633905 is a reply to message #633900] Fri, 27 February 2015 04:26 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
mist598 wrote on Fri, 27 February 2015 09:46
Quote:
Looks like you want to max the date and sum the amounts. What's the problem?

Hi cookiemonster, after used max date , got the result as shown in the 1st post


The results in the first post were filed under the output needed.

You have got to stop asking half a question.
Re: need the out put based on max date [message #633906 is a reply to message #633902] Fri, 27 February 2015 04:32 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well then you're going to have to actually explain the logic that leads to your result.
Re: need the out put based on max date [message #633909 is a reply to message #633906] Fri, 27 February 2015 05:14 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
SQL> create table xxc_transctions(INVENTORY_ITEM_ID number,LAST_INVOICED_DATE date,  COST_AMOUNT  number,SALES_AMOUNT nu
mber);

Table created.

SQL> insert into xxc_transctions(1234,to_date('7/14/2014'),  -410, -1086.5);
insert into xxc_transctions(1234,to_date('7/14/2014'),  -410, -1086.5)
                            *
ERROR at line 1:
ORA-00928: missing SELECT keyword


SQL> insert into xxc_transctions(1234,to_date('6/23/2014'),  -1335,-3537.75);
insert into xxc_transctions(1234,to_date('6/23/2014'),  -1335,-3537.75)
                            *
ERROR at line 1:
ORA-00928: missing SELECT keyword


SQL> insert into xxc_transctions(1234,to_date('5/14/2014'),  0, 0);
insert into xxc_transctions(1234,to_date('5/14/2014'),  0, 0)
                            *
ERROR at line 1:
ORA-00928: missing SELECT keyword


SQL> insert into xxc_transctions(1234,to_date('5/23/2014'),  0,0);
insert into xxc_transctions(1234,to_date('5/23/2014'),  0,0)
                            *
ERROR at line 1:
ORA-00928: missing SELECT keyword
Re: need the out put based on max date [message #633912 is a reply to message #633909] Fri, 27 February 2015 06:24 Go to previous messageGo to next message
saipavan.plsql
Messages: 17
Registered: February 2015
Location: chennai
Junior Member
select max(INVENTORY_ITEM_ID),max(LAST_INVOICED_DATE),sum(COST_AMOUNT),sum(SALES_AMOUNT) from xxc_transctions;
hi i am new to oracle upto my knowledge by using this query we can get the output what he is asking for
Re: need the out put based on max date [message #633913 is a reply to message #633912] Fri, 27 February 2015 07:10 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Based on the OPs last response that isn't what he's looking for.
Re: need the out put based on max date [message #633914 is a reply to message #633902] Fri, 27 February 2015 08:23 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
mist598 wrote on Fri, 27 February 2015 04:53
Quote:
Does that mean your problem is solved?

No not solved. I applied Max fun but no use, how to do this can you please help me?

SALES_AMOUNT is based on months like Jan, feb ...


It's amazing how dense you are that after 1000+ posts you do not know how to ask a question or understand that no one know what you are talking about based on what limited info you ALWAYS post. Not once have you ever posted a question that can be answered with a single response. How do people at your job deal with you?
Re: need the out put based on max date [message #633915 is a reply to message #633914] Fri, 27 February 2015 08:34 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
+1
Previous Topic: how to return a row based on count using multiple tables
Next Topic: Need to calculate a Set No. from a Data set
Goto Forum:
  


Current Time: Thu Mar 28 17:28:03 CDT 2024