/* List all patient information */

SELECT *
FROM Patients, PatAddr, PatAge
WHERE Patients.Dob = PatAge.Dob and Patients.Zip = PatAddr.Zip and ( Patients.SSNo = "@Prompt" or (Patients.LastName = "@LastName" and Patients.FirstName = "@FirstName"))

/* List current patients */

SELECT SSNo, LastName, FirstName, RoomNumber, BedNumber, Account.DateIn
FROM Patients, Accounts, BedAssignments
WHERE Patients.SSNo = Accounts.SSNo and Patients.SSNo = Bed_Assignments.SSNo and Accounts.DateOut = NULL

/* Given doctors name list all patients visited. */

SELECT Patients.SSNo, Patients.LastName, Patients.FirstName, Date, Fee, Diagnosis
FROM Patients, Examines
WHERE SSNo=PSSNo and DSSNo in (SELECT SSNo
FROM Doctors
WHERE FirstName= "@FirstName" and LastName = "@LastName)

/* Given Doctors name list all patients he is currently treating. */

SELECT PSSNo, Patients.LastName, Patients.FirstName, Date, Fee, Diagnosis
FROM Patients, Examines, Accounts
WHERE Accounts.SSNo= Patients.SSNo and Patients.SSNo=PSSNo and Accounts.DateOut = NULL
And
DSSNo in (SELECT SSNo
FROM Doctors
WHERE FirstName= "@FirstName" and LastName = "@LastName and )

/* Admit a patient

/* Given SSNo Search for past Accounts.

SELECT *
FROM Patients
WHERE SSNo = "@SSNo" into TEMP

/* Enter patient Data into input screen. */

If TEMP = Null then
INSERT into Patients
VALUES( @LastName, @FirstName, @SSNo, etc.*)
INSERT into ACCOUNTSSELECT
ELSE
UPDATE Patients
Set LastName="@LastName", FirstName= "@FirstName" etc.
WHERE Patients.SSNo = "@SSNO"
End

/* Add new Account Record */

INSERT into Accounts
VALUES ( DateIn= "Current Date", SSNo="@SSNo")

/* end of Admitting Patient */

/* Checkout patient */

SELECT BedAssignments
WHERE DateOut = Null and SSNo = "@SSNo"
AS TEMP
If TEMP <> NULL then
Call transfer Patient out of Bed
END
UPDATE Accounts
SET Account.Amount = (SELECT ( SUM(BedAssignment.Amounts) + SUM(Examines.Fees))
FROM Examines, BedAssignements, Accounts
WHERE SSNo = "@SSNo" and BedAssignments.DateIn >= Account.DateIn
and Examines.Ssno = Patients.SSNo and Accounts.SSNo = Patients.SSNo)
WHERE Accounts.SSNo = "@SSNo"

/* end of Check out patient */

/* Transfer out of bed */

/* Transfer into bed */

/* Transfer to new bed */