Home » SQL & PL/SQL » SQL & PL/SQL » Date calculation..effective ways
Date calculation..effective ways [message #225858] Wed, 21 March 2007 14:32 Go to next message
orausern
Messages: 817
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 Go to previous messageGo to next message
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 #225953 is a reply to message #225866] Thu, 22 March 2007 01:33 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What about

select trunc(add_months(birthdate, 12 * 65) - 1, 'MM)

No case or decode required.
Re: Date calculation..effective ways [message #225964 is a reply to message #225953] Thu, 22 March 2007 02:08 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Frank, Rajaram,

This is simply awesome! Very thankful for these inputs..shall get back to you if I have some question..

Fantastic support!!
Nirav
Re: Date calculation..effective ways [message #226030 is a reply to message #225964] Thu, 22 March 2007 06:18 Go to previous messageGo to next message
orausern
Messages: 817
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 #226040 is a reply to message #226030] Thu, 22 March 2007 06:59 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
50,000 rows shouldn't be a problem.
Re: Date calculation..effective ways [message #226052 is a reply to message #226040] Thu, 22 March 2007 07:28 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Hi

Getting the error:
ERROR at line 11:
ORA-01779: cannot modify a column which maps to a non key-preserved table

when trying to do the update as Rajaram suggested...can u plz suggest a work around...Rajaram , as you suggested , the workaround would be to use corelated subquery..can you plz suggest how to go about writing the subquery?

with thanks,
Nirav
Re: Date calculation..effective ways [message #226184 is a reply to message #226052] Thu, 22 March 2007 14:47 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Frank,

Someone suggested
SELECT birthdate + INTERVAL '65'
YEAR

at:
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/531a2d6e6fa233ce/67f65eb13b4ac3f8?hl=en#67f65eb13b4a c3f8

just fyi...
Re: Date calculation..effective ways [message #226243 is a reply to message #226184] Fri, 23 March 2007 01:26 Go to previous message
Frank
Messages: 7880
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
Previous Topic: Pl/SQl bulk update versus SQl update
Next Topic: i want to delete records (merged)
Goto Forum:
  


Current Time: Wed Dec 07 09:08:41 CST 2016

Total time taken to generate the page: 0.17342 seconds