Home » SQL & PL/SQL » SQL & PL/SQL » Any suggestions
Any suggestions [message #290773] Mon, 31 December 2007 13:11 Go to next message
Arcane
Messages: 7
Registered: December 2007
Location: Netherland
Junior Member
Is there anyway, somebody can give me any suggestions, to develope the code for this application.

The Study Day variables (*_DY) describe the relative day of the observation starting with the reference date as Day 1. They are determined by comparing the date portion of the respective date/time variables (*_START_DTC, and *_END_DTC) to the date portion of the Reference Start Date (REF_START_DTC from the Main table).

The Reference Start Date (REF_START_DTC) is designated as Study Day 1. The Study Day value is incremented by 1 for each date following REF_START_DTC. Dates prior to REF_START_DTC are decremented by 1, with the date preceding REF_START_DTC designated as Study Day -1 (there is no Study Day 0).

All Study Day values are integers. Thus, to calculate Study Day (*_DY):

•*_DY = (date portion of *_DTC) – (date portion of REF_START_DTC) + 1 if *_DTC is on or after REF_START_DTC

•*_DY = (date portion of *_DTC) – (date portion of REF_START_DTC) if *_DTC precedes REF_START_DTC

This algorithm should be used across all topic tables.

A series of tables are modeled in the schema to help fulfill the above business requirement. Their data dictionary information is as follows:

describe main

Name          Type
------------- -------------
PATIENT_ID    VARCHAR2(50)
REF_START_DTC VARCHAR2(200)

describe tobacco

Name              Type
----------------- -------------
PATIENT_ID        VARCHAR2(50)
TOBACCO_START_DTC VARCHAR2(200)
TOBACCO_END_DTC   VARCHAR2(200)
TOBACCO_START_DY  NUMBER
TOBACCO_END_DY    NUMBER

describe medication

Name                 Type
-------------------- -------------
PATIENT_ID           VARCHAR2(50)
MEDICATION_START_DTC VARCHAR2(200)
MEDICATION_END_DTC   VARCHAR2(200)
MEDICATION_START_DY  NUMBER
MEDICATION_END_DY    NUMBER

…

describe <topic>

Name                 Type
-------------------- -------------
PATIENT_ID        VARCHAR2(50)
<TOPIC>_START_DTC VARCHAR2(200)
<TOPIC>_END_DTC   VARCHAR2(200)
<TOPIC>_START_DY  NUMBER
<TOPIC>_END_DY    NUMBER


---apply the following technical assumptions:

•The database is Oracle 10g.

•PATIENT_ID is a unique patient identifier. It is also a primary key in the Main table, as well as a foreign key to the rest of the topic tables.

•Values in all *_DTC columns, when populated, are in this format: YYYY-MM-DD"T"HH24:MI:SS, e.g., 2007-12-28T13:15:30 to represent 1:15:30pm on December 28th, 2007.

•Although in the proper format, values in any *_DTC columns are not always representing valid calendar date (i.e., dirty data). For example, 2007-02-31T08:00:00 (Note: February does not have 31 days). When a *_DTC value is blank or does not represent a valid calendar date, the corresponding *_DY should be blank.

•A topic table can have a range of 50 to 500,000 rows of records.

•The PL/SQL procedure should:

oAllow the users to specify the name of a topic table as a parameter

oAll *_DY columns of all records in the given topic table should be populated in one procedure call, where permitted by the algorithm.



Thanks in Advance

[Updated on: Mon, 31 December 2007 13:13] by Moderator

Report message to a moderator

Re: Any suggestions [message #290774 is a reply to message #290773] Mon, 31 December 2007 13:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
What do you want from us?
To do your work?
Post what you already did or try.

Of course, this is a bad design, you should have only 1 table for all topics (possibly partitioned by topic).

Take care your lines of code don't exceed 80 characters. Use the "Preview" button.

Regards
Michel

[Updated on: Mon, 31 December 2007 13:20]

Report message to a moderator

Re: Any suggestions [message #290858 is a reply to message #290774] Tue, 01 January 2008 12:13 Go to previous messageGo to next message
Arcane
Messages: 7
Registered: December 2007
Location: Netherland
Junior Member
Thanks Micheal for taking interest in solving my problem. THis is my first post on this website, so plz ignore any mistake i have done regarding the rules of posting the question.
So far behonest with you, i am not getting anything else except the use of bind variable logic from this rek. I think alot of things are missing from this rek. Like not sure which is Source table, what logic they wana use for the rest of the columns of topic table. There is no any sample data.

Do u agree ?

Once again very thankful

Re: Any suggestions [message #290859 is a reply to message #290858] Tue, 01 January 2008 12:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You'll find the forum rules in OraFAQ Forum Guide. For instance, it also talks about IM speak.

If this is the only specifications you have, yes many things are missing.

Regards
Michel
Re: Any suggestions [message #292063 is a reply to message #290773] Mon, 07 January 2008 10:58 Go to previous messageGo to next message
xpert
Messages: 1
Registered: January 2008
Junior Member
looks interesting piece of puzzle, anyone got up with some thing on this?
Re: Any suggestions [message #292065 is a reply to message #292063] Mon, 07 January 2008 11:14 Go to previous message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Yes if you can explain the whole specifications.

Regards
Michel
Previous Topic: merge - how to idenitfy the number of rows inserted and updated
Next Topic: Update statement using case with two tables
Goto Forum:
  


Current Time: Tue Feb 11 03:47:56 CST 2025