How to create a VIEW selecting data from multiple tables for a ranged time interval

Plural · #1 (**permalink**) 10-26-11, 19:40

I have the Tables Patient, Doctor, Treatment, Pat_Doc_Treat, and Schedule.

Patient(patientNumb, SS, name, dateOfBirth, address, dateAdmitted, clinic)
Doctor(doctorID, name, pagerNumb)
Treatment(treatmentCode, desciption)
Pat_Doc_Treat(patientNumb, doctorID, treatmentCode, date, comments)
Schedule(doctorID, date, clinic, numbOfWorkHours)

The task is:
A doctor, including the doctor's ID, name, and number of patients treated by the doctor in the clinic for a given date interval.

My question is:
How do you create a VIEW to select certain data from multiple tables for a ranged time interval?

I tried creating this view:

Code:

CREATE VIEW V_Doc AS
SELECT d.doctor.ID, d.name, count(pdt.*)
FROM Doctor d, Pat_Doc_Treat pdt
WHERE d.doctorID=pdt.doctorID AND pdt.treatmentCode AND pdt.date IN(SELECT date FROM Schedule);

Is it better to write a stored procedure for this or is a view able to fulfill my question?

Thank you.