Home » SQL & PL/SQL » SQL & PL/SQL » URGENT : Help needed for Stored procedures
URGENT : Help needed for Stored procedures [message #35896] Mon, 22 October 2001 18:24 Go to next message
Bhavesh Doshi
Messages: 2
Registered: October 2001
Junior Member
Hi,

I want to create a stored procedure which gives me record set as output. My problem is described as under...

I have two tables. One is APPOINTMENT Table and the other is WORKTIMEDEFAULT table. The WORKTIMEDEFAULT table has daywise working time of office stored in Number format and the interval is also specified. The APPOINTMENT Table is having appointment for the day.

Now I want to find a particular day's appointment with all available empty slots. The empty slots entries are not stored anywhere, that values we need to populate using APPOINTMENT table's booked appointments.

The structures of both the tables are as under.....

SQL> desc appointment
Name Type
----------------------------------------- ------------
APPTMNTID NUMBER(10)
FIRSTNAME VARCHAR2(20)
LASTNAME VARCHAR2(20)
ADDRESS1 VARCHAR2(60)
ADDRESS2 VARCHAR2(60)
CITY VARCHAR2(40)
STATE VARCHAR2(30)
ZIPCODE VARCHAR2(10)
PATIENTID NUMBER(12)
APPTMNTSTATUS VARCHAR2(15)
APPTMNTTYPE VARCHAR2(15)
APPOINTMENTREASONID NUMBER(10)
WORKPHONE VARCHAR2(20)
HOMEPHONE VARCHAR2(20)
MEMO VARCHAR2(255)
MOBILEPHONE VARCHAR2(20)
INSORGNAME VARCHAR2(50)
INSPLANNAME VARCHAR2(50)
PCP VARCHAR2(1)
EMAIL VARCHAR2(80)
CLINICID NUMBER(10)
LOCATIONID NUMBER(10)
STAFFID NUMBER(10)
APPTRESOURCEID NUMBER(10)
STARTTIME DATE
ENDTIME DATE
COMPLAINT VARCHAR2(30)
EPISODE VARCHAR2(30)
REFERREDBY VARCHAR2(30)
MADEBY VARCHAR2(30)
PATIENTNO VARCHAR2(10)

SQL> desc worktimedefault
Name Type
----------------------------------------- ------------
WORKTIMEDEFAULTID NUMBER(10)
DAY VARCHAR2(10)
AMFROM NUMBER(18)
AMTO NUMBER(18)
PMFROM NUMBER(18)
PMTO NUMBER(18)
CLINICID NUMBER(10)
LOCATIONID NUMBER(10)
APNTMNTRSRCID NUMBER(28)

---------------------------------

Can anyone please help me in writting this store procedure..... Earliest reply will be greatly appreciated.....

Thanks & regards,

Bhavesh Doshi

----------------------------------------------------------------------

Report message to a moderator

Re: URGENT : Help needed for Stored procedures [message #35903 is a reply to message #35896] Tue, 23 October 2001 01:34 Go to previous messageGo to next message
Milan Kumar Barui
Messages: 16
Registered: October 2001
Junior Member
Write this procedure first, if any modification or error comes, then only I will try.
Sorry for inconvenience.
Milan


----------------------------------------------------------------------

Report message to a moderator

Reposting: URGENT : Help needed for Stored procedures [message #35997 is a reply to message #35896] Mon, 29 October 2001 17:23 Go to previous message
Bhavesh
Messages: 7
Registered: July 2001
Junior Member
Hi,
I want to create a stored procedure which gives me record set as output. My problem is described as under...

I have two tables. One is APPOINTMENT Table and the other is WORKTIMEDEFAULT table. The WORKTIMEDEFAULT table has daywise working time of office stored in Number format and the interval is also specified. The APPOINTMENT Table is having appointment for the day.

Now I want to find a particular day's appointment with all available empty slots. The empty slots entries are not stored anywhere, that values we need to populate using APPOINTMENT table's booked appointments.

The structures of both the tables are as under.....

SQL> desc appointment
Name Type
----------------------------------------- ------------
APPTMNTID NUMBER(10)
FIRSTNAME VARCHAR2(20)
LASTNAME VARCHAR2(20)
ADDRESS1 VARCHAR2(60)
ADDRESS2 VARCHAR2(60)
CITY VARCHAR2(40)
STATE VARCHAR2(30)
ZIPCODE VARCHAR2(10)
PATIENTID NUMBER(12)
APPTMNTSTATUS VARCHAR2(15)
APPTMNTTYPE VARCHAR2(15)
APPOINTMENTREASONID NUMBER(10)
WORKPHONE VARCHAR2(20)
HOMEPHONE VARCHAR2(20)
MEMO VARCHAR2(255)
MOBILEPHONE VARCHAR2(20)
INSORGNAME VARCHAR2(50)
INSPLANNAME VARCHAR2(50)
PCP VARCHAR2(1)
EMAIL VARCHAR2(80)
CLINICID NUMBER(10)
LOCATIONID NUMBER(10)
STAFFID NUMBER(10)
APPTRESOURCEID NUMBER(10)
STARTTIME DATE
ENDTIME DATE
COMPLAINT VARCHAR2(30)
EPISODE VARCHAR2(30)
REFERREDBY VARCHAR2(30)
MADEBY VARCHAR2(30)
PATIENTNO VARCHAR2(10)

SQL> desc worktimedefault
Name Type
----------------------------------------- ------------
WORKTIMEDEFAULTID NUMBER(10)
DAY VARCHAR2(10)
AMFROM NUMBER(18)
AMTO NUMBER(18)
PMFROM NUMBER(18)
PMTO NUMBER(18)
CLINICID NUMBER(10)
LOCATIONID NUMBER(10)
APNTMNTRSRCID NUMBER(28)

---------------------------------

Can anyone please help me in writting this store procedure..... Earliest reply will be greatly appreciated.....

Thanks & regards,

Bhavesh Doshi

----------------------------------------------------------------------

Report message to a moderator

Previous Topic: Alter a populated table to add a not null field.
Next Topic: How can I to made the records in one of my tables doubled every 5 minutes with a trigger?
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Dec 25 11:24:46 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.