Home » SQL & PL/SQL » SQL & PL/SQL » Preform math calculation upon dates
Preform math calculation upon dates [message #493624] Wed, 09 February 2011 02:29 Go to next message
Sarlula
Messages: 18
Registered: September 2010
Junior Member
I got 4 fields:
event_date (01/01/1900 hh:mm:ss)
team_count (number)
interview_count (number)
duration (01/01/1900 hh:mm:ss) - duration of each interview


The calculation is:
(interview_count * team_count * duration(hh:mm)) + event_date
i.e.:
(3 * 2 * 02:30) + 01/01/1900 08:00:00

= 15:00:00 + 01/01/1900 08:00:00

= 01/01/1900 23:00:00.


all I care about is the hh:mm in the result.
How can I preform this calculation in pl/sql?
I tried all possible ways I could think of..

Re: Preform math calculation upon dates [message #493631 is a reply to message #493624] Wed, 09 February 2011 02:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68264
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What are the type of the columns?

Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel
Re: Preform math calculation upon dates [message #493633 is a reply to message #493631] Wed, 09 February 2011 03:00 Go to previous messageGo to next message
Sarlula
Messages: 18
Registered: September 2010
Junior Member
the types are:
event_date DATE
team_count NUMBER
interview_count NUMBER
duration DATE
Re: Preform math calculation upon dates [message #493639 is a reply to message #493633] Wed, 09 February 2011 03:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68264
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How a duration could a date? It should be an INTERVAL (or a NUMBER).

Please post the test case I requested.

Regards
Michel
Re: Preform math calculation upon dates [message #493648 is a reply to message #493639] Wed, 09 February 2011 04:04 Go to previous messageGo to next message
Sarlula
Messages: 18
Registered: September 2010
Junior Member
The duration is a date (its an old DB).

the test-code is:
create table testme
(
       event_date DATE,
       team_count NUMBER,
       interview_count NUMBER,
       duration DATE
);



insert into testme (event_date, team_count, interview_count, duration) 
       VALUES ('01-jan-1900 08:00:00', 2, 3, '01-jan-1900 02:30:00');




select extract(hour FROM
        to_timestamp(to_char(to_date(
         (
          (e.duration * e.team_count * e.summonscount) + e.event_date),
           'dd-mon-yyyy HH24:MI:SS'),
           'dd-mon-yyyy HH24:MI:SS')))
  from testme;



I want the output to be: 23.
Re: Preform math calculation upon dates [message #493651 is a reply to message #493648] Wed, 09 February 2011 04:18 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
If you subtract two dates you get the difference in days. When you multiply that difference in days with 24 you get the difference in hours.

Example:

SELECT (duration   - Trunc(duration))   * 24,  
       (event_date - Trunc(event_date)) * 24 
  FROM testme 


Then you can multiply/add those numbers at will.
Re: Preform math calculation upon dates [message #493652 is a reply to message #493648] Wed, 09 February 2011 04:23 Go to previous message
Michel Cadot
Messages: 68264
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select event_date +
  2         (team_count * interview_count * to_number(to_char(duration,'SSSSS')))
  3         / 86400
  4  from testme
  5  /
EVENT_DATE+(TEAM_COU
--------------------
01-jan-1900 23:00:00

Regards
Michel
Re: Preform math calculation upon dates [message #493653 is a reply to message #493648] Wed, 09 February 2011 04:23 Go to previous message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
SQL> select to_number(to_char(new_date, 'HH24')) new_date
  2  FROM (select event_date 
  3               + (team_count * interview_count * (duration - trunc(duration))) AS new_date
  4        from testme);

  NEW_DATE
----------
        23

SQL> 

[Updated on: Wed, 09 February 2011 09:39] by Moderator

Report message to a moderator

Previous Topic: UTL File
Next Topic: Arguments to Procedure
Goto Forum:
  


Current Time: Fri Aug 19 11:52:53 CDT 2022