Home » SQL & PL/SQL » SQL & PL/SQL » current date - preovious date calculations
| current date - preovious date calculations [message #667368] |
Tue, 26 December 2017 02:27  |
 |
sshree12
Messages: 20
Registered: December 2017
|
Junior Member |
|
|
create table t (PO varchar2(30), LineNum int, as_of_date Date, Total int)
with the following data:
insert into t values ( 1, 4, to_date('02-JUN-2008'),550)
insert into t values ( 1, 5, to_date('02-AUG-2008'),3075)
insert into t values ( 1, 6, to_date('09-SEP-2008'),352)
insert into t values ( 1, 6, to_date('10-OCT-2008'),352)
insert into t values ( 1, 7, to_date('12-NOV-2008'),950)
i wanted to do total of current minus previous month wise
For calculation- Last testing done on NOV and testing amount-950 and before that testing done on 10 october 2008 352 so my gain is 500-400 = 100 and if there is no testing inJULY THEN 3075-550 /2(months) becomes average
|
|
|
|
| Re: current date - preovious date calculations [message #667369 is a reply to message #667368] |
Tue, 26 December 2017 03:05  |
 |
Michel Cadot
Messages: 68510
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.
TO_DATE must always be followed by a format specification:
SQL> insert into t values ( 1, 4, to_date('02-JUN-2008'),550);
insert into t values ( 1, 4, to_date('02-JUN-2008'),550)
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
Quote:Last testing done on NOV and testing amount-950 and before that testing done on 10 october 2008 352 so my gain is 500-400 = 100
Where does come 500 and 400?
Assuming there can be at most one line per month (for each PO):
SQL> col po format a2
SQL> col gain format 999990.00
SQL> select po, linenum, as_of_date, total,
2 ( total
3 - lag(total,1,0) over (partition by po order by as_of_date) )
4 / nvl(trunc(months_between(as_of_date,
5 lag(as_of_date) over (partition by po order by as_of_date))),
6 1) gain
7 from t
8 order by po, as_of_date
9 /
PO LINENUM AS_OF_DATE TOTAL GAIN
-- ---------- ------------------- ---------- ----------
1 4 02/06/2008 00:00:00 550 550.00
1 5 02/08/2008 00:00:00 3075 1262.50
1 6 09/09/2008 00:00:00 352 -2723.00
1 6 10/10/2008 00:00:00 352 0.00
1 7 12/11/2008 00:00:00 950 598.00
5 rows selected.
[Updated on: Tue, 26 December 2017 03:05] Report message to a moderator |
|
|
|
| Re: current date - preovious date calculations [message #667370 is a reply to message #667369] |
Tue, 26 December 2017 03:08 |
 |
Littlefoot
Messages: 21783
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
A less sophisticated way than Michel's:
SQL> with test (po, line_num, as_of_date, total) as
2 (select 1, 4, date '2008-06-02', 550 from dual union
3 select 1, 5, date '2008-08-02', 3075 from dual union
4 select 1, 6, date '2008-09-09', 352 from dual union
5 select 1, 6, date '2008-10-10', 352 from dual union
6 select 1, 7, date '2008-11-12', 950 from dual
7 ),
8 lager as
9 (select as_of_date,
10 lag (as_of_date) over (partition by po order by as_of_date) prev_date,
11 total,
12 lag(total) over (partition by po order by as_of_date) prev_total
13 from test
14 order by as_of_date
15 )
16 select as_of_date,
17 case when abs(to_number(to_char(as_of_date, 'mm')) -
18 to_number(to_char(prev_date, 'mm'))) > 1
19 then (total - nvl(prev_total, 0)) / 2
20 else total - nvl(prev_total, 0)
21 end result
22 from lager
23 order by as_of_date;
AS_OF_DATE RESULT
---------- ----------
02.06.2008 550
02.08.2008 1262,5
09.09.2008 -2723
10.10.2008 0
12.11.2008 598
SQL> |
|
|
|
|
|
|
|
|
|
|
|
| Re: current date - preovious date calculations [message #667376 is a reply to message #667374] |
Tue, 26 December 2017 03:39 |
|
sshree12
Messages: 20
Registered: December 2017
|
Junior Member |
|
|
ID date amount1 amount2 amount3 type
a 28-Jul-15 1614.7 95.98250929 2423035.491 gi
a 8-Aug-15 1614.7 101.9578293 1791262.02 pi
a 1-Jan-15 30.00239642 2789858.93 ci
a 1-Jan-15 1514.7 30 300000 mi
a 15-Sep-06 1000.7 162.6922402 2975967.241 gi
b 22-Sep-06 942.7 113.5939789 2943124.598 pi
b 6-Oct-06 884.7 118.0094259 2886974.273 ci
b 10-Nov-06 855.7 97.32852876 3250786.003 mi
b 5-Dec-06 826.7 74.27008321 3409278.242 mi
thanks
[mod-edit: code tags added by bb]
[Updated on: Wed, 27 December 2017 20:56] by Moderator Report message to a moderator |
|
|
|
| Re: current date - preovious date calculations [message #667377 is a reply to message #667376] |
Tue, 26 December 2017 03:41 |
|
sshree12
Messages: 20
Registered: December 2017
|
Junior Member |
|
|
ID date amount1 amount2 amount3 type
a 28-Jul-15 1614.7 95.98250929 2423035.491 gi
a 8-Aug-15 1614.7 101.9578293 1791262.02 pi
a 1-Jan-15 30.00239642 2789858.93 ci
a 1-Jan-15 1514.7 30 300000 mi
a 15-Sep-06 1000.7 162.6922402 2975967.241 gi
b 22-Sep-06 942.7 113.5939789 2943124.598 pi
b 6-Oct-06 884.7 118.0094259 2886974.273 ci
b 10-Nov-06 855.7 97.32852876 3250786.003 ci
b 5-Dec-06 826.7 74.27008321 3409278.242 ci
[mod-edit: code tags added by bb]
[Updated on: Wed, 27 December 2017 20:57] by Moderator Report message to a moderator |
|
|
|
| Re: current date - preovious date calculations [message #667378 is a reply to message #667377] |
Tue, 26 December 2017 03:46 |
|
sshree12
Messages: 20
Registered: December 2017
|
Junior Member |
|
|
create table t (PO varchar2(30), amount1 int,amount2 int,amount3 int, as_of_date Date,type)
with the following data:
insert into t values ( a, 400, 100,50 ,to_date('02-JUN-2008'),550,pi)
insert into t values ( a, 500 200,100,to_date('02-AUG-2008'),3075,pi)
insert into t values ( a, 600, 300,150,to_date('09-SEP-2008'),352,ci)
insert into t values ( b, 700, 400,200,to_date('10-OCT-2008'),352,gi)
insert into t values ( b, 800, 500,300,to_date('12-NOV-2008'),950ci,)
select id,date, case when type=gi then amount2 when type=pi then amount1 when type=ci then amount3) as actual from table1 and then want use actul as whole column for above Lag calculation
|
|
|
|
|
|
| Re: current date - preovious date calculations [message #667380 is a reply to message #667378] |
Tue, 26 December 2017 03:48 |
|
sshree12
Messages: 20
Registered: December 2017
|
Junior Member |
|
|
sending insert statement again,
insert into t values ( 'a', 400, 100,50 ,to_date('02-JUN-2008'),550,'pi')
insert into t values ( 'a', 500 200,100,to_date('02-AUG-2008'),3075,'pi')
insert into t values ( 'a', 600, 300,150,to_date('09-SEP-2008'),352,'ci')
insert into t values ( 'b', 700, 400,200,to_date('10-OCT-2008'),352,'gi')
insert into t values ( 'b', 800, 500,300,to_date('12-NOV-2008'),950,'ci',)
|
|
|
|
|
|
|
|
|
|
| Re: current date - preovious date calculations [message #667388 is a reply to message #667385] |
Tue, 26 December 2017 05:10 |
|
Littlefoot
Messages: 21783
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
STOP!
Simply - stop doing that. You're flooding the forum with useless information, posting bunch of messages that are difficult to follow, which are very similar to each other and provide incorrect directions.
So - take a deep breath, make a clear picture in your head, create a meaningful message, use [code] tags to make it readable and - by all means - make it correct (as you keep posting "my gain is 500-400 = 100" while your sample data don't contain those values at all). We aren't mind readers, you know.
Apart from that, don't create a new topic for the same discussion; I've already locked (and will delete) your new topic, and will do the same if you continue doing that - or, even worse, I might temporarily restrict your access to this site (read: ban you for a few hours).
|
|
|
|
| Re: current date - preovious date calculations [message #667390 is a reply to message #667388] |
Tue, 26 December 2017 06:35 |
|
sshree12
Messages: 20
Registered: December 2017
|
Junior Member |
|
|
I am writing the info as short summary as mentioned below,
create table t (PO varchar2(30), amount1 int,amount2 int,amount3 int, as_of_date Date,type)
with the following data:
insert into t values ( 'a', 400, 100,50 ,to_date('02-JUN-2008'),550,'pi')
insert into t values ( 'a', 500 200,100,to_date('02-AUG-2008'),3075,'pi')
insert into t values ( 'a', 600, 300,150,to_date('09-SEP-2008'),352,'ci')
insert into t values ( 'b', 700, 400,200,to_date('10-OCT-2008'),352,'gi')
insert into t values ( 'b', 800, 500,300,to_date('12-NOV-2008'),950,'ci',)
select id,date, case when type=gi then amount2 when type=pi then amount1 when type=ci then amount3) as actual from table1 and then want use actul as whole column for below Lag calculation
i wanted to do total of current minus previous month wise
For calculation- Last testing done on NOV and testing amount-950 and before that testing done on 10 october 2008 352 so my gain is 950-352 = 608
|
|
|
|
|
|
| Re: current date - preovious date calculations [message #667433 is a reply to message #667392] |
Wed, 27 December 2017 21:59 |
 |
Barbara Boehmer
Messages: 9062
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The following is my attempt to decipher this mess, based on worded description, ignoring seemingly mismatched numeric values that have been mentioned.
-- cleaned-up statements for table creation and data insertion that poster should have tested and fixed:
SCOTT@orcl_12.1.0.2.0> create table t
2 (PO varchar2(10),
3 amount1 int,
4 amount2 int,
5 amount3 int,
6 as_of_date Date,
7 Total int,
8 type varchar2(4))
9 /
Table created.
SCOTT@orcl_12.1.0.2.0> insert all
2 into t values ( 'a', 400, 100, 50 , to_date('02-06-2008','DD-MM-YYYY'), 550, 'pi')
3 into t values ( 'a', 500, 200, 100, to_date('02-08-2008','DD-MM-YYYY'), 3075, 'pi')
4 into t values ( 'a', 600, 300, 150, to_date('09-09-2008','DD-MM-YYYY'), 352, 'ci')
5 into t values ( 'b', 700, 400, 200, to_date('10-10-2008','DD-MM-YYYY'), 352, 'gi')
6 into t values ( 'b', 800, 500, 300, to_date('12-11-2008','DD-MM-YYYY'), 950, 'ci')
7 select * from dual
8 /
5 rows created.
-- cleaned-up query that poster should have tested and fixed:
SCOTT@orcl_12.1.0.2.0> select po, as_of_date, total,
2 case when type='gi' then amount2
3 when type='pi' then amount1
4 when type='ci' then amount3
5 end as actual
6 from t
7 order by po, as_of_date
8 /
PO AS_OF_DATE TOTAL ACTUAL
---------- --------------- ---------- ----------
a Mon 02-Jun-2008 550 400
a Sat 02-Aug-2008 3075 500
a Tue 09-Sep-2008 352 150
b Fri 10-Oct-2008 352 400
b Wed 12-Nov-2008 950 300
5 rows selected.
-- using actual column for lag calculation by using above as inline view (sub-query in the from clause):
SCOTT@orcl_12.1.0.2.0> select po, as_of_date, total, actual,
2 lag(actual,1,0) over (partition by po order by as_of_date) lag_actual
3 from (select po, as_of_date, total,
4 case when type='gi' then amount2
5 when type='pi' then amount1
6 when type='ci' then amount3
7 end as actual
8 from t)
9 order by po, as_of_date
10 /
PO AS_OF_DATE TOTAL ACTUAL LAG_ACTUAL
---------- --------------- ---------- ---------- ----------
a Mon 02-Jun-2008 550 400 0
a Sat 02-Aug-2008 3075 500 400
a Tue 09-Sep-2008 352 150 500
b Fri 10-Oct-2008 352 400 0
b Wed 12-Nov-2008 950 300 400
5 rows selected.
-- substituting the above in query previously provided by Michel:
SCOTT@orcl_12.1.0.2.0> select po, as_of_date, total, actual,
2 lag(actual,1,0) over (partition by po order by as_of_date) lag_actual,
3 (total - lag(actual,1,0) over (partition by po order by as_of_date))
4 / nvl(trunc(months_between(as_of_date, lag(as_of_date) over (partition by po order by as_of_date))),1) gain
5 from (select po, as_of_date, total,
6 case when type='gi' then amount2
7 when type='pi' then amount1
8 when type='ci' then amount3
9 end as actual
10 from t)
11 order by po, as_of_date
12 /
PO AS_OF_DATE TOTAL ACTUAL LAG_ACTUAL GAIN
---------- --------------- ---------- ---------- ---------- ----------
a Mon 02-Jun-2008 550 400 0 550
a Sat 02-Aug-2008 3075 500 400 1337.5
a Tue 09-Sep-2008 352 150 500 -148
b Fri 10-Oct-2008 352 400 0 352
b Wed 12-Nov-2008 950 300 400 550
5 rows selected.
|
|
|
|
Goto Forum:
Current Time: Sat Sep 30 14:59:21 CDT 2023
|
] 
Blog
Search
Help
Members
Register
Login
Home
