Any suggestions [message #290773] |
Mon, 31 December 2007 13:11  |
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   |
 |
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   |
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
|
|
|
|
|
|