Home » SQL & PL/SQL » SQL & PL/SQL » comparing amounts (10g)
comparing amounts [message #333292] Fri, 11 July 2008 02:55 Go to next message
beetel
Messages: 96
Registered: April 2007
Member
I have a table:

seq_id, unit_id, amount, run_date
  1       101      1000   02-Jul-2008
  1       102      1500   02-Jul-2008
  1       103       500   02-Jul-2008
  2       101      3000   03-Jul-2008
  2       102      4000   03-Jul-2008
  2       103      2500   03-Jul-2008
  3       101      6000   04-Jul-2008
  3       102      3000   04-Jul-2008
  3       103      4500   04-Jul-2008



Everyday, I would like to get the difference in amount for each unit.
For example, today is 4-Jul-2008, I would like to see its difference in amount compared to previous day (that is July 3). So, I should have something like:

unit_id   difference  
101            3000
102           -1000
103            2000


Please help me with the SQL. By the way, here is the SQL to create the records:

create table MyTab (seq_id number, unit_id number, amount number, run_date date);
insert into MyTab values (1,       101,      1000,   '02-Jul-2008');
insert into MyTab values (1,       102,      1500,   '02-Jul-2008');
insert into MyTab values (1,       103,       500,   '02-Jul-2008');
insert into MyTab values (2,       101,      3000,   '03-Jul-2008');
insert into MyTab values (2,       102,      4000,   '03-Jul-2008');
insert into MyTab values (2,       103,      2500,   '03-Jul-2008');
insert into MyTab values (3,       101,      6000,   '04-Jul-2008');
insert into MyTab values (3,       102,      3000,   '04-Jul-2008');
insert into MyTab values (3,       103,      4500,   '04-Jul-2008');
commit;




Re: comparing amounts [message #333298 is a reply to message #333292] Fri, 11 July 2008 03:09 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
'02-Jul-2008' is a string not a date:
SQL> create table MyTab (seq_id number, unit_id number, amount number, run_date date);

Table created.

SQL> insert into MyTab values (1,       101,      1000,   '02-Jul-2008');
insert into MyTab values (1,       101,      1000,   '02-Jul-2008')
                                                     *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected


Use LAG function.

Regards
Michel

[Updated on: Fri, 11 July 2008 03:10]

Report message to a moderator

Previous Topic: how to exchange the non partiton table to partition table
Next Topic: find version number of oracle
Goto Forum:
  


Current Time: Sun Dec 11 00:28:37 CST 2016

Total time taken to generate the page: 0.08813 seconds