1.Now that you have all of the tables and data you need, here are the instructions for the rest of the project.
2.Create a single SQL script with the tables, PKs, FKs, inserts for the data provided in the Excel spreadsheet and the following views and stored procedures, at a minimum.
3.Create the view that will show all patients at all sites for both studies. You can do this together or 1 view for each study.
4.Create the view that will show all randomized patients, their site and their treatment for both studies. You can do this together or 1 view for each study.
5.Create the view that will show the next available random codes (MIN) for both studies. You can do this together or 1 view for each study.
6.Create the view that will show all available drug at all sites for both studies. You can do this together or 1 view for each study.
7.Create the view that will show all withdrawn patients, their site, withdrawal date and withdrawal reason for both studies.
8.Create other views as needed. Put as much as possible into Views so you are pulling from them instead of from tables.
9.Create the stored procedure(s) that will screen a patient for both studies. You can do this together or 1 for each study.
10.Create the stored procedure(s) that will randomize a patient for both studies. You can do this together or 1 for each study. This will include a stored procedure for obtaining a random code as well as a drug kit.
11.Create the stored procedure(s) that will withdraw a patient for both studies. You can do this together or 1 for each study. Remember a patient can go from Screening Visit to Withdrawal without being randomized. This will be up to the Doctor. Your code just has to be able to do it.
12.The last item on the list is the calls to the stored procedures. You need to provide these on a SEPARATE .sql file called CallsToStoredProcs.sql. In this script, you will have the following calls to your stored procs.
a)8 patients for each study for screening.
b)5 patients randomized for each study. (including assigning drug kit)
c)4 patients (2 randomized and 2 not randomized patients) withdrawn from each study.
d) These calls are like what you did in the stored procedures assignment.
DECLARE @intPatientID AS INTEGER = 0;
EXECUTE uspAddPatient @intPatientID OUTPUT, 111001, 2, '1/1/1962', 2, 205
Here are some hints that will hopefully help you create this database.
1.There are several intrinsic functions within SQL Server you should take advantage of. MIN, MAX, COUNT, RANDOM, CASE WHEN THEN, others as needed.
2.When writing your stored procedures use Cursors to pull data needed. IE if you need to know which study a patient is in so you pull the correct random code. Use something like this
DECLARE @StudyID as INT
Begin
DECLARE StudyCursor CURSOR LOCAL FOR
SELECT StudyID FROM V_PATIENT_STUDY
WHERE PatientID = @intPatientID
OPEN StudyCursor
FETCH FROM StudyCursor
INTO @StudyID
End
3.When you pull any random code or drug kit, for either study, always use the lowest number available that matches the criteria you need. This should be handled in #5 and #6 above.
4.One way to get the random code for study 54321 is to use RAND() and generate a number between 1 and 0. Then if it is <= .5 make it Placebo. If it is > .5 make it Active. This is not easy but there is a lot of information available if you search for random number generators in SQL Server.