Home » SQL & PL/SQL » SQL & PL/SQL » current date - preovious date calculations
current date - preovious date calculations Tue, 26 December 2017 02:27
 sshree12 Messages: 20Registered: 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: 65971Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator

Welcome to the forum.
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```
Not all people have the same default format and speak the same language.

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: 21446Registered: June 2005 Location: Croatia, Europe Senior MemberAccount 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 #667371 is a reply to message #667370] Tue, 26 December 2017 03:11
 sshree12 Messages: 20Registered: December 2017 Junior Member
yes but lag difference should be max six months and not any years
Re: current date - preovious date calculations [message #667372 is a reply to message #667371] Tue, 26 December 2017 03:18
 sshree12 Messages: 20Registered: December 2017 Junior Member
yes but lag difference should be max six months and not in years i.e. if the date is 2 years old and all
Re: current date - preovious date calculations [message #667373 is a reply to message #667372] Tue, 26 December 2017 03:20
 sshree12 Messages: 20Registered: December 2017 Junior Member
if there is no testing inJULY THEN 3075-550 /2(months) becomes average it is results for July not August
Re: current date - preovious date calculations [message #667374 is a reply to message #667373] Tue, 26 December 2017 03:26
 Michel Cadot Messages: 65971Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator

Don't understand what you mean, post a new test case that covers all cases and post complete result you want for it.

Re: current date - preovious date calculations [message #667376 is a reply to message #667374] Tue, 26 December 2017 03:39
 sshree12 Messages: 20Registered: 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
```
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 mentioned calculation
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: 20Registered: 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: 20Registered: 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 #667379 is a reply to message #667378] Tue, 26 December 2017 03:48
 Michel Cadot Messages: 65971Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator

As you refuse to follow what I said I leave this topic.

Re: current date - preovious date calculations [message #667380 is a reply to message #667378] Tue, 26 December 2017 03:48
 sshree12 Messages: 20Registered: 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 #667381 is a reply to message #667380] Tue, 26 December 2017 03:49
 sshree12 Messages: 20Registered: December 2017 Junior Member
new test case means?
Re: current date - preovious date calculations [message #667384 is a reply to message #667381] Tue, 26 December 2017 03:54
 sshree12 Messages: 20Registered: December 2017 Junior Member

ok posted a new case for this
https://www.orafaq.com/forum/m/667383/#msg_667383
Re: current date - preovious date calculations [message #667385 is a reply to message #667384] Tue, 26 December 2017 04:06
 sshree12 Messages: 20Registered: December 2017 Junior Member
done can you please check this
https://www.orafaq.com/forum/m/667383/#msg_667383
Re: current date - preovious date calculations [message #667388 is a reply to message #667385] Tue, 26 December 2017 05:10
 Littlefoot Messages: 21446Registered: June 2005 Location: Croatia, Europe Senior MemberAccount 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: 20Registered: 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 #667392 is a reply to message #667390] Tue, 26 December 2017 10:44
 Littlefoot Messages: 21446Registered: June 2005 Location: Croatia, Europe Senior MemberAccount Moderator
Unfortunately, you did almost nothing of what Michel or I asked you to.
• CREATE TABLE is invalid; what is the last "type" word doing there? It appears that table T has 5 or maybe 6 columns
• INSERT INTO contain 7 values; how are we supposed to fit 7 values into a table with 5 or 6 columns?
• The second INSERT is invalid as it misses a comma between 500 and 200
• A INSERTS are invalid as none of TO_DATE contains format mask; JUN or AUG etc. are invalid in my database, it speaks Croatian
• SELECT you posted is invalid; strings aren't enclosed into single quotes. It selects values from some "table1" whose description is unknown to us. Maybe you meant "T" table, but - it doesn't contain columns like ID or DATE (which is, by the way, an invalid column name)
• 950 - 352 = 598; how did you get 608?
I'm sorry, but I'm not going to spend any more time on this. If you're lucky, someone else will assist.
Re: current date - preovious date calculations [message #667433 is a reply to message #667392] Wed, 27 December 2017 21:59
 Barbara Boehmer Messages: 8904Registered: 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.
```
 Previous Topic: over partition by Next Topic: last date of the year
Goto Forum:

Current Time: Sun Nov 18 16:36:58 CST 2018