Home » SQL & PL/SQL » SQL & PL/SQL » Date calculation..effective ways
 Date calculation..effective ways [message #225858] |
Wed, 21 March 2007 14:32  |
orausern
Messages: 826
Registered: December 2005
|
Senior Member |
|
|
Hi ,
I have a requirement to do some date arithmatic and looking for some inputs on doing this in an effective way...basically the birthdate of a person is the input...from that we have to find the date on which his 65th birthday comes-say X...now if X is on 1st of a month then our 'special date' -Say Y- is a month prior to this X. But if X is not on 1st of a month, then Y is to be 1st of the month(like if X is 24-March-2040 then Y is 01-March-2040 but if X is 01-March-2040 then Y is 01-February-2040). The table contains a few ten thousands of records. So to calculate this date I used the following table and madeup this query:
table emp1 (same structure as the famous emp table of the scott schema) + birth_date(date) --an extra field added to this table.
and the query I wrote to find the special date is:
select decode(to_number(to_char(add_months(birth_date,780),'DD')),1,ADD_MONTHS(birth_date,799),
trunc(add_months(birth_date,780)) ) from emp1
Now this is working but is this the best way to do it or is there a better way....another question, this special date has to be updated in another table (say emp2 for example- some date field of emp2) then what would be an efficient way to use pl/sql to do this?
With thanks,
Nirav
[Updated on: Wed, 21 March 2007 14:35] Report message to a moderator |
|
|
|
| Re: Date calculation..effective ways [message #225866 is a reply to message #225858] |
Wed, 21 March 2007 15:37  |
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
|
Senior Member |
|
|
increment_month := (65 * 12);
If to_char(supplied_birth_date,'dd') = 1
then
increment_month := increment_month - 1;
End If;
If the above is correct your example is not saying that
select decode(to_number(to_char(add_months(birth_date,780),'DD')),1,ADD_MONTHS(birth_date,799),
trunc(add_months(birth_date,780)) ) from emp1
Because you are adding the months by 799 whereas it should be 779.
it could be coded this way
select hiredate, case trunc(hiredate) when trunc(hiredate,'mm') then add_months(trunc(hiredate,'mm'), (65 * 12) - 1) else add_months(trunc(hiredate,'mm'), (65 * 12) ) end from emp
Sample Output :
HIREDATE CASETRUNC(H
----------- -----------
17-DEC-1980 01-DEC-2045
20-FEB-1981 01-FEB-2046
22-FEB-1981 01-FEB-2046
02-APR-1981 01-APR-2046
28-SEP-1981 01-SEP-2046
01-MAY-1981 01-APR-2046
09-JUN-1981 01-JUN-2046
19-APR-1987 01-APR-2052
17-NOV-1981 01-NOV-2046
08-SEP-1981 01-SEP-2046
23-MAY-1987 01-MAY-2052
HIREDATE CASETRUNC(H
----------- -----------
03-DEC-1981 01-DEC-2046
03-DEC-1981 01-DEC-2046
23-JAN-1982 01-JAN-2047
Next Question arises is why case and not decode.
Courtesy asktom.oracle.com
4) You know, performance wise I haven't benchmarked it properly. CASE seems to be
marginally slower in 8i doing things that DECODE can do (eg:
( case when object_type = 'INDEX' then 1
when object_type = 'TABLE' then 1
when object_type = 'PROCEDURE' then 1
else 0
end )
is better as:
decode( object_type, 'INDEX', 1, 'TABLE', 1, 'PROCEDURE', 1, 0 )
but in 9i with the searched cased expression:
( case object_type
when 'INDEX' then 1
when 'TABLE' then 1
when 'PROCEDURE' then 1
else 0
end )
they are about the same when doing the same things.
Logic wise, it is night and day. Decode is somewhat obscure -- CASE is very very clear.
Things that are easy to do in decode are easy to do in CASE, things that are hard or near
impossible to do with decode are easy to do in CASE. CASE, logic wise, wins hands down.
> "this special date has to be updated in another table (say emp2 for example- some date field of emp2) then what would be an efficient way to use pl/sql to do this?"
Pl/Sql is not needed for this. You can do this using update as long as the table you are not modifying is key-preserved.
Example is
SQL> desc emp2;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NUMBER(4)
DOB DATE
SQL> select * from emp2;
EMPNO DOB
---------- -----------
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
EMPNO DOB
---------- -----------
7900
7902
7934
SQL> update
(select emp2.dob dob, case trunc(hiredate)
when trunc(hiredate,'mm')
then
add_months(trunc(hiredate,'mm'), (65 * 12) - 1)
else
add_months(trunc(hiredate,'mm'), (65 * 12) )
end new_dob
from emp, emp2
where emp.empno = emp2.empno)
set dob = new_dob;
14 rows updated.
SQL> select * from emp2;
EMPNO DOB
---------- -----------
7369 01-DEC-2045
7499 01-FEB-2046
7521 01-FEB-2046
7566 01-APR-2046
7654 01-SEP-2046
7698 01-APR-2046
7782 01-JUN-2046
7788 01-APR-2052
7839 01-NOV-2046
7844 01-SEP-2046
7876 01-MAY-2052
EMPNO DOB
---------- -----------
7900 01-DEC-2046
7902 01-DEC-2046
7934 01-JAN-2047
As i said earlier this update will work only when the table you are not modifying is key-preserved ( In this case it is emp table). Otherwise you will hit the following error:
SQL> update
2 (select emp2.dob dob, case trunc(hiredate)
3 when trunc(hiredate,'mm')
4 then
5 add_months(trunc(hiredate,'mm'), (65 * 12) - 1)
6 else
7 add_months(trunc(hiredate,'mm'), (65 * 12) )
8 end new_dob
9 from emp, emp2
10 where emp.empno = emp2.empno)
11 set dob = new_dob;
set dob = new_dob
*
ERROR at line 11:
ORA-01779: cannot modify a column which maps to a non key-preserved table
To get around this problem if you cannot add a primary key other workaround is to use a correlated subquery. It will be my last option to use pl/sql if you cannot attain your desired result using sql.
Just another way of doing it.
HTH
[Updated on: Wed, 21 March 2007 15:56] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Date calculation..effective ways [message #226030 is a reply to message #225964] |
Thu, 22 March 2007 06:18 |
orausern
Messages: 826
Registered: December 2005
|
Senior Member |
|
|
One question I had was...the table which is to be updated can have upto 50000 rows..in that case doing it by SQL is ok? or would it use too much rollback etc? -should be use SQL only for updating 50000 rows or should be somehow generate batches of maybe 1000 and then do the update?
I will be able to check how much time and resources this takes in a few hours and upload the findings , but wanted to know your views in the mean time...
Thanks again!
Nirav
[Updated on: Thu, 22 March 2007 06:38] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Date calculation..effective ways [message #226243 is a reply to message #226184] |
Fri, 23 March 2007 01:26 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
Instead of add_months(birthdate, 12*65) ?
It's a good alternative, very readable. I am not used to the INTERVAL constructs..
But the rest of my construct should stay the same: subtract 1 day and trunc to the month
|
|
|
|
Goto Forum:
Current Time: Sat Mar 15 19:32:16 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
