Why should you care?

Clinical data is sensitive and must stay consistent across appointments and meds.

See it live — copy this example

Run in SQL Server Management Studio (SSMS) or Azure Data Studio.

USE DataVerse;
IF OBJECT_ID(N'dbo.Patients', N'U') IS NOT NULL DROP TABLE dbo.Patients;
CREATE TABLE dbo.Patients (
    PatientId INT IDENTITY PRIMARY KEY,
    Mrn VARCHAR(20) NOT NULL UNIQUE,
    FullName NVARCHAR(100) NOT NULL,
    Dob DATE NOT NULL
);
IF OBJECT_ID(N'dbo.Encounters', N'U') IS NOT NULL DROP TABLE dbo.Encounters;
CREATE TABLE dbo.Encounters (
    EncounterId INT IDENTITY PRIMARY KEY,
    PatientId INT NOT NULL REFERENCES dbo.Patients(PatientId),
    SeenAt DATETIME2 NOT NULL,
    Department NVARCHAR(50) NOT NULL
);
SELECT p.Mrn, p.FullName, COUNT(e.EncounterId) AS Visits
FROM dbo.Patients p
LEFT JOIN dbo.Encounters e ON e.PatientId = p.PatientId
GROUP BY p.Mrn, p.FullName;

What happened?

• Patients identified by MRN; Encounters record visits.
• The aggregate shows visit counts per patient for an ops board.

Practice next

1. Create Patients and Encounters.
2. Insert two patients and encounters.
3. Run visit counts.
4. Filter Department = N'Cardiology'.
5. Add a Prescriptions table FK to EncounterId.

Remember

MRN uniquely identifies patients. Encounters capture visits. Privacy features are mandatory.

Clinic visit board

Hospital ops tracks DataVerse encounters per MRN.

Outcome: Front desk sees revisit patterns quickly.