Home » SQL & PL/SQL » SQL & PL/SQL » Pl/Sql Procedure
Pl/Sql Procedure [message #291258] Thu, 03 January 2008 09:45 Go to next message
Arcane
Messages: 7
Registered: December 2007
Location: Netherland
Junior Member
I have these 2 tables main table and medication table. I need to create any procedure which can perform this task. Any hint or tip would be appreciated.

• Medi_start_dy = (date portion of main table ref_start_DTC) – (date portion of medication table MEDI_START_DTC) + 1 if MEDI_START_DTC is on or after REF_START_DTC

• Medi_start_dy = (date portion of main table ref_start_DTC) – (date portion of medication table MEDI_START_DTC) if MEDI_START_DTC precedes REF_START_DTC

Main Table
PATIENT_ID	REF_START_DTC
001	        2007-04-01T05:00:00
002	        2007-05-01T08:00:00
003	        2006-00-00T03:45:00


Medication Table
PATIENT_ID	MEDI_START_DTC	      MEDI_START_DY
001	        2007-03-31T16:00:00	-1
001	 	 
001	 	 
002	        2007-05-30T03:45:00	30
002	        2007-03-30T08:00:00	-32
002	        2007-02-31T17:15:00	 
003	        2007-01-15T04:15:00	 

Re: Pl/Sql Procedure [message #291262 is a reply to message #291258] Thu, 03 January 2008 09:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:

I need to create any procedure which can perform this task.

Which one?

What did you already try?

Also please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).
Always post a meaningful title.

Regards
Michel


[Updated on: Thu, 03 January 2008 09:51]

Report message to a moderator

Re: Pl/Sql Procedure [message #291268 is a reply to message #291262] Thu, 03 January 2008 10:05 Go to previous messageGo to next message
Arcane
Messages: 7
Registered: December 2007
Location: Netherland
Junior Member
Oracle 9.2.0.0

I need to calculate the Medi_start_dy of the Medication table by using the Logic under below.

• Medi_start_dy = (date portion of main table ref_start_DTC) – (date portion of medication table MEDI_START_DTC) + 1 if MEDI_START_DTC is on or after REF_START_DTC

• Medi_start_dy = (date portion of main table ref_start_DTC) – (date portion of medication table MEDI_START_DTC) if MEDI_START_DTC precedes REF_START_DTC

Thanks and Rgds
Re: Pl/Sql Procedure [message #291270 is a reply to message #291268] Thu, 03 January 2008 10:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You have the formulas, what is the problem?

Regards
Michel
Re: Pl/Sql Procedure [message #291272 is a reply to message #291270] Thu, 03 January 2008 10:13 Go to previous messageGo to next message
Arcane
Messages: 7
Registered: December 2007
Location: Netherland
Junior Member
Any help please, IF i need to use this formula in the loop.
Re: Pl/Sql Procedure [message #291282 is a reply to message #291272] Thu, 03 January 2008 10:54 Go to previous message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
I still don't understand where is your problem.
Actually do you want us to write the query for you?

Regards
Michel
Previous Topic: Date
Next Topic: dynamic column alias using sysdate
Goto Forum:
  


Current Time: Fri Feb 14 16:42:06 CST 2025