/* Solve
**transaction base queries, must use go statement between queries as everything rolls back after execution
#Visit
status
0 = pending
1 = active
2 = not active
1. Search for all patients with last name "smith"
2. Search for all patients that has an active visit
3. Insert a new patient and display the patient
4. Insert a new visit and display the visit
5. Update a record of your choice and display the results
6. Search for all patients with at least 20 notes
7. Display all patients: First name, middle name, last name with a count of their notes, minimum date of service, maximum date of service, visit status as text based on above status values
*/
/* Table Creation */
CREATE TABLE Patient
(
ID INT IDENTITY NOT NULL Primary key,
FirstName NVARCHAR(MAX),
MiddleName NVARCHAR(MAX),
LastName NVARCHAR(MAX)
)
CREATE TABLE Visit
(
VisitID INT IDENTITY NOT NULL Primary key,
PatientID INT NOT NULL,
AdmitDate DATETIME,
[Status] INT NOT NULL DEFAULT(0),
FOREIGN KEY (PatientID) REFERENCES Patient(ID)
)
CREATE TABLE Notes
(
ID INT IDENTITY NOT NULL Primary key,
VisitID INT,
DateOfService datetime,
[Description] NVARCHAR(max),
Deleted BIT NOT NULL DEFAULT(0),
FOREIGN KEY (VisitID) REFERENCES Visit(VisitID)
)
/* Data Population */
INSERT INTO PATIENT(FirstName, MiddleName, LastName)
VALUES ('John', 'S', 'Smith'), ('Jane', 'L', 'Doe'), ('Tom',null,'Jones'),('Mary','M','Smith')
INSERT INTO Visit(PatientID, AdmitDate,[status])
select ID, (select DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)), ID%3 from patient order by ID
declare @max int = 100, @I int = 0
while @I < @max
BEGIN
INSERT INTO Notes (VisitID, DateOfService, [Description])
select VisitID, (ABS(CHECKSUM(NEWID())) % 65530), newid() from visit where (select rand()) > .65 and visitID = 1
INSERT INTO Notes (VisitID, DateOfService, [Description])
select VisitID, (ABS(CHECKSUM(NEWID())) % 65530), newid() from visit where (select rand()) > .85 and visitID = 2
INSERT INTO Notes (VisitID, DateOfService, [Description])
select VisitID, (ABS(CHECKSUM(NEWID())) % 65530), newid() from visit where (select rand()) > .52 and visitID = 3
INSERT INTO Notes (VisitID, DateOfService, [Description])
select VisitID, (ABS(CHECKSUM(NEWID())) % 65530), newid() from visit where (select rand()) > .33 and visitID = 4
SET @I = @I+1
end
GO
/* 1. */
GO
/* 2. */
GO
/* 3. */
GO
/* 4. */
GO
/* 5. */
GO
/* 6. */
GO
/* 7. */
GO
Telegram: DB developers channel