SQLize Online / PHPize Online  /  SQLtest Online

/* @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ @@ IMPORTANT - Each of my courses uses its own subset of the data and tables from this projcet. @@ @@ If you are looking for the practice demo database I use in my courses, follow these links: @@ @@ 1. Query Processing - https://github.com/ami-levin/LinkedIn/tree/master/Query%20Processing/Demo%20Database @@ @@ 2. Window Functions - https://github.com/ami-levin/LinkedIn/tree/master/Window%20Functions/Demo%20Database @@ @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ */ -- Animal shelter tables and data USE Animal_Shelter; GO -- Variable assignment DECLARE @Shelter_Open DATE = '20160101'; -- Shelter open day DECLARE @Last_Data_Day DATE = '20191231'; -- Last day of data DECLARE @Shelter_State VARCHAR(20) = 'California'; -- Shelter state DECLARE @Shelter_County VARCHAR(20) = 'Los Angeles';-- Shelter county - cities and addresses will be limited to the same county DECLARE @Max_Zip_Code CHAR(5) = '91000'; -- Further limit persons to zip areas below this number DECLARE @Max_Street_Number INT = 1000; -- Maximal number used to generate street address DECLARE @Min_Person_Age INT = 18; -- Minimum age of person as of shelter open DECLARE @Max_Person_Age INT = 70; -- Maximum age of person as of shelter open DECLARE @Num_Persons INT = 120; -- Number of persons DECLARE @Num_Veterinarians INT = 2; -- Number of staff vets DECLARE @Num_Assistants INT = 4; -- Number of staff assistants DECLARE @Num_receptionists INT = 2; -- Number of staff receptionists DECLARE @Num_Managers INT = 1; -- Number of managers DECLARE @Num_Dogs INT = 60; -- Number of dogs in shelter DECLARE @Num_Cats INT = 30; -- Number of cats in shelter DECLARE @Num_Rabbits INT = 10; -- Number of rabbits in shelter DECLARE @Num_Adoptions INT = 70; -- Number of total adoptions DECLARE @Min_Animal_Age_D INT = 1; -- Minimal age in days as of admission DECLARE @Max_Animal_Age_D INT = 18 * 12 * 30; -- Maximal age in days as of admission DECLARE @Min_Adoption_Fee INT = 50; -- Minimal adoption fee DECLARE @Max_Adoption_fee INT = 100; -- Maximal adoption fee DECLARE @Percent_Non_Breed INT = 75; -- Percent of non breed animals BEGIN TRANSACTION; -- easy rollback -- Operational data -- Persons CREATE TABLE Persons ( Email VARCHAR(100) NOT NULL PRIMARY KEY, First_Name VARCHAR(15) NOT NULL, Last_Name VARCHAR(15) NOT NULL, Birth_Date DATE NULL, Address VARCHAR(100) NOT NULL, State VARCHAR(20) NOT NULL, City VARCHAR(30) NOT NULL, Zip_Code CHAR(5) NOT NULL, ); WITH All_Possible_Names AS ( SELECT CASE Genders.Gender WHEN 'M' THEN CPN.Male ELSE CPN.Female END AS First_Name, CPN1.Surname AS Last_Name FROM Reference.Common_Person_Names AS CPN CROSS JOIN Reference.Common_Person_Names AS CPN1 CROSS JOIN (VALUES ('F'), ('M')) AS Genders(Gender) ) INSERT INTO Persons (Email, First_Name, Last_Name, Birth_Date, Address, State, City, Zip_Code) SELECT LOWER(Random_Names.First_Name) + '.' + LOWER(Random_Names.Last_name) + '@' + CASE dbo.Random(1, 6) WHEN 1 THEN 'gmail' WHEN 2 THEN 'gmail' -- Gmail is more popular than other mail providers WHEN 3 THEN 'hotmail' WHEN 4 THEN 'yahoo' WHEN 5 THEN 'outlook' ELSE 'icloud' END + '.com' AS Email, Random_Names.First_Name, Random_Names.Last_Name, CASE WHEN dbo.Random(1,10) > 1 -- 10% did not provide birth date THEN DATEADD(DAY, dbo.Random(0, 365), DATEADD(YEAR, -dbo.Random(@Min_Person_Age, @Max_Person_Age), @Shelter_Open)) ELSE NULL END AS Birth_Date, Addresses.Address, @Shelter_State, Cities.City, Cities.Zip_Code FROM ( SELECT APN.First_Name, APN.Last_Name FROM All_Possible_Names AS APN ORDER BY NEWID() OFFSET 0 ROWS FETCH NEXT @Num_Persons ROWS ONLY ) AS Random_Names CROSS APPLY ( SELECT CZC.City, CZC.Zip_Code FROM Reference.City_Zip_Codes AS CZC INNER JOIN Reference.Cities AS C ON C.State = CZC.State AND C.City = CZC.City WHERE C.State = @Shelter_State AND C.County = @Shelter_County AND (CZC.Zip_Code < @Max_Zip_Code OR @Max_Zip_Code IS NULL) AND Random_Names.First_Name IS NOT NULL -- Force per row execution ORDER BY NEWID() OFFSET 0 ROWS FETCH NEXT 1 ROW ONLY ) AS Cities(City, Zip_Code) CROSS APPLY ( SELECT CAST(dbo.Random(1, @Max_Street_Number) AS VARCHAR(4)) + ' ' + CSN.Street FROM Reference.Common_Street_Names AS CSN WHERE Random_Names.First_Name IS NOT NULL -- Force per row execution ORDER BY NEWID() OFFSET 0 ROWS FETCH NEXT 1 ROW ONLY ) AS Addresses(Address); -- Staff roles CREATE TABLE Staff_Roles ( Role VARCHAR(20) NOT NULL PRIMARY KEY ); INSERT INTO Staff_Roles (Role) VALUES ('Receptionist'), ('Veterinarian'), ('Assistant'), ('Manager'), ('Janitor'); -- Latter will not be assigned, everyone helps clean up -- Staff CREATE TABLE Staff ( Email VARCHAR(100) NOT NULL PRIMARY KEY REFERENCES Persons (Email) ON UPDATE CASCADE ON DELETE NO ACTION, Hire_Date DATE NOT NULL ); INSERT INTO Staff (Email, Hire_Date) SELECT Email, DATEADD(DAY, dbo.Random(0, DATEDIFF(DAY, @Shelter_Open, @Last_Data_Day)), @Shelter_Open) FROM Persons ORDER BY NEWID() OFFSET 0 ROWS FETCH NEXT (@Num_receptionists + @Num_Managers + @Num_Assistants + @Num_Veterinarians) ROWS ONLY; -- Staff use 'animalshelter.com' domain addresses UPDATE Persons SET Email = LEFT(Email, CHARINDEX('@', Email)) + 'animalshelter.com' WHERE Email IN (SELECT Email FROM Staff); -- Staff roles CREATE TABLE Staff_Assignments ( Email VARCHAR(100) NOT NULL REFERENCES Staff (Email) ON UPDATE CASCADE ON DELETE NO ACTION, Role VARCHAR(20) NOT NULL REFERENCES Staff_Roles (Role) ON UPDATE CASCADE ON DELETE NO ACTION, Assigned DATE NOT NULL, PRIMARY KEY (Email, Role), INDEX NCIDX_FK_Staff_Assignments__Roles(Role) ); INSERT INTO Staff_Assignments (Email, Role, Assigned) SELECT S.Email, 'Veterinarian', S.Hire_Date FROM Staff AS S ORDER BY NEWID() OFFSET 0 ROWS FETCH NEXT @Num_Veterinarians ROWS ONLY; INSERT INTO Staff_Assignments (Email, Role, Assigned) SELECT S.Email, 'Assistant', S.Hire_Date FROM Staff AS S WHERE Email NOT IN ( SELECT Email FROM Staff_Assignments ) ORDER BY NEWID() OFFSET 0 ROWS FETCH NEXT (@Num_Assistants) ROWS ONLY; INSERT INTO Staff_Assignments (Email, Role, Assigned) SELECT S.Email, 'Receptionist', S.Hire_Date FROM Staff AS S WHERE Email NOT IN ( SELECT Email FROM Staff_Assignments ) ORDER BY NEWID() OFFSET 0 ROWS FETCH NEXT (@Num_receptionists) ROWS ONLY; INSERT INTO Staff_Assignments (Email, Role, Assigned) SELECT S.Email, 'Manager', S.Hire_Date FROM Staff AS S WHERE Email NOT IN ( SELECT Email FROM Staff_Assignments ) ORDER BY NEWID() OFFSET 0 ROWS FETCH NEXT @Num_Managers ROWS ONLY; -- 1 of each role on day 1 WITH Staff_Rn AS ( SELECT *, ROW_NUMBER() OVER(PARTITION BY role ORDER BY Assigned ASC) AS Rn FROM Staff_Assignments ) UPDATE Staff_Rn SET Staff_Rn.Assigned = @Shelter_Open WHERE Rn = 1; -- update hire dates to match UPDATE Staff SET Hire_Date = (SELECT Assigned FROM Staff_Assignments AS SA WHERE SA.Email = Staff.Email); -- Animals CREATE TABLE Animals ( Name VARCHAR(20) NOT NULL, Species VARCHAR(10) NOT NULL, Primary_Color VARCHAR(10) NOT NULL REFERENCES Reference.Colors (Color), CONSTRAINT PK_Animals PRIMARY KEY (Name, Species), -- Business rule = unique identification of animal as name + species will do for a small sample set -- probably not enough for a real world scenario but depends on shelter naming conventions Implant_Chip_ID UNIQUEIDENTIFIER NOT NULL UNIQUE, -- This is the 'most natural' key, but it's unfamiliar and not very useful for human communication Breed VARCHAR(50) NULL, Gender CHAR(1) NOT NULL CHECK (Gender IN ( 'M', 'F' )), -- no need for gender fluidity in animals :-) Birth_Date DATE NOT NULL, Pattern VARCHAR(20) NOT NULL, CONSTRAINT FK_Animals__Patterns FOREIGN KEY (Species, Pattern) REFERENCES Reference.Patterns (Species, Pattern), Admission_Date DATE NOT NULL, CONSTRAINT FK_Animals__Breeds FOREIGN KEY (Species, Breed) REFERENCES Reference.Breeds (Species, Breed), INDEX NCIDX_FK_Animals__Patterns (Species, Pattern), INDEX NCIDX_FK_Animals__Breeds (Species, Breed), INDEX NCIDX_FK_Animals__Colors (Primary_Color) ); -- Dogs WITH Unpivoted_Names AS ( SELECT CAN.Species, Genders.Gender, CASE WHEN Genders.Gender = 'F' THEN CAN.Female ELSE CAN.Male END AS Name FROM Reference.Common_Animal_Names AS CAN CROSS JOIN (VALUES ('F'), ('M')) AS Genders (Gender) ), DeDuped_F_M_Names -- There are identical names for both M and F of the same species which we want to avoid since gender is not part of key AS ( SELECT UN.Species, CASE WHEN dbo.Random(1, 2) = 1 THEN MAX(Gender) ELSE MIN(Gender) -- Pick arbitraty gender when duplicated END AS Gender, UN.Name FROM Unpivoted_Names AS UN GROUP BY UN.Species, UN.Name ) INSERT INTO Animals (Implant_Chip_ID, Species, Breed, Name, Gender, Birth_Date, Primary_Color, Pattern, Admission_Date) SELECT NEWID() AS Implant_Chip_ID, D.Species, B.Breed, -- Non breeds first D.Name, D.Gender, '20001010' AS Birth_Date, -- Place holder, will update later based on generated admission date C.Color, CP.Pattern, DATEADD(DAY, dbo.Random(0, DATEDIFF(DAY, @Shelter_Open, @Last_Data_Day)), @Shelter_Open) AS Admission_Date FROM DeDuped_F_M_Names AS D CROSS APPLY ( SELECT Color FROM Reference.Colors AS C ORDER BY NEWID(), D.Name OFFSET 0 ROWS FETCH NEXT 1 ROW ONLY ) AS C CROSS APPLY ( SELECT Pattern FROM Reference.Patterns AS P WHERE P.Species = D.Species ORDER BY NEWID(), C.Color OFFSET 0 ROWS FETCH NEXT 1 ROW ONLY ) AS CP CROSS APPLY ( SELECT Breed FROM Reference.Breeds AS B WHERE B.Species = D.Species ORDER BY NEWID(), C.Color OFFSET 0 ROWS FETCH NEXT 1 ROW ONLY ) AS B(Breed) WHERE D.Species = 'Dog' ORDER BY NEWID() OFFSET 0 ROWS FETCH NEXT (@Num_Dogs) ROWS ONLY; -- Cats WITH Unpivoted_Names AS ( SELECT CAN.Species, Genders.Gender, CASE WHEN Genders.Gender = 'F' THEN CAN.Female ELSE CAN.Male END AS Name FROM Reference.Common_Animal_Names AS CAN CROSS JOIN (VALUES ('F'), ('M')) AS Genders (Gender) ), DeDuped_F_M_Names -- There are identical names for both M and F of the same species-avoid since we don't have gender in key AS ( SELECT UN.Species, CASE WHEN dbo.Random(1, 2) = 1 THEN MAX(Gender) ELSE MIN(Gender) -- Pick arbitraty gender when duplicated END AS Gender, UN.Name FROM Unpivoted_Names AS UN GROUP BY UN.Species, UN.Name ) INSERT INTO Animals (Implant_Chip_ID, Species, Breed, Name, Gender, Birth_Date, Primary_Color, Pattern, Admission_Date) SELECT NEWID() AS Implant_Chip_ID, D.Species, B.Breed, -- Non breeds first D.Name, D.Gender, '20001010' AS Birth_Date, -- Place holder, will update later based on generated admission date CASE WHEN B.Breed LIKE '%Blue%' THEN 'Gray' ELSE C.Color END, CP.Pattern, DATEADD(DAY, dbo.Random(0, DATEDIFF(DAY, @Shelter_Open, @Last_Data_Day)), @Shelter_Open) AS Admission_Date FROM DeDuped_F_M_Names AS D CROSS APPLY ( SELECT Color FROM Reference.Colors AS C ORDER BY NEWID(), D.Name OFFSET 0 ROWS FETCH NEXT 1 ROW ONLY ) AS C CROSS APPLY ( SELECT Pattern FROM Reference.Patterns AS P WHERE P.Species = D.Species ORDER BY NEWID(), C.Color, D.Name OFFSET 0 ROWS FETCH NEXT 1 ROW ONLY ) AS CP CROSS APPLY ( SELECT Breed FROM Reference.Breeds AS B WHERE B.Species = D.Species ORDER BY NEWID(), C.Color, D.Name, CP.Pattern OFFSET 0 ROWS FETCH NEXT 1 ROW ONLY ) AS B(Breed) WHERE D.Species = 'Cat' ORDER BY NEWID() OFFSET 0 ROWS FETCH NEXT (@Num_Cats) ROWS ONLY; -- Rabbits WITH Unpivoted_Names AS ( SELECT CAN.Species, Genders.Gender, CASE WHEN Genders.Gender = 'F' THEN CAN.Female ELSE CAN.Male END AS Name FROM Reference.Common_Animal_Names AS CAN CROSS JOIN (VALUES ('F'), ('M')) AS Genders (Gender) ), DeDuped_F_M_Names -- There are identical names for both M and F of the same species-avoid since we don't have gender in key AS ( SELECT UN.Species, CASE WHEN dbo.Random(1, 2) = 1 THEN MAX(Gender) ELSE MIN(Gender) -- Pick arbitraty gender when duplicated END AS Gender, UN.Name FROM Unpivoted_Names AS UN GROUP BY UN.Species, UN.Name ) INSERT INTO Animals (Implant_Chip_ID, Species, Breed, Name, Gender, Birth_Date, Primary_Color, Pattern, Admission_Date) SELECT NEWID() AS Implant_Chip_ID, D.Species, B.Breed, -- Non breeds first D.Name, D.Gender, '20001010' AS Birth_Date, -- Place holder, will update later based on generated admission date C.Color, CP.Pattern, DATEADD(DAY, dbo.Random(0, DATEDIFF(DAY, @Shelter_Open, @Last_Data_Day)), @Shelter_Open) AS Admission_Date FROM DeDuped_F_M_Names AS D CROSS APPLY ( SELECT Color FROM Reference.Colors AS C ORDER BY NEWID(), D.Name OFFSET 0 ROWS FETCH NEXT 1 ROW ONLY ) AS C CROSS APPLY ( SELECT Pattern FROM Reference.Patterns AS P WHERE P.Species = D.Species ORDER BY NEWID(), C.Color OFFSET 0 ROWS FETCH NEXT 1 ROW ONLY ) AS CP CROSS APPLY ( SELECT Breed FROM Reference.Breeds AS B WHERE B.Species = D.Species ORDER BY NEWID(), C.Color OFFSET 0 ROWS FETCH NEXT 1 ROW ONLY ) AS B(Breed) WHERE D.Species = 'Rabbit' ORDER BY NEWID() OFFSET 0 ROWS FETCH NEXT (@Num_Rabbits) ROWS ONLY; -- Most animals are non breed UPDATE Animals SET Breed = CASE WHEN dbo.Random(1, 100) > @Percent_Non_Breed THEN Breed ELSE NULL END; -- Now update to 'real' birth dates based on generated admission date UPDATE Animals SET Birth_Date = DATEADD(DAY, -dbo.Random(@Min_Animal_Age_D, @Max_Animal_Age_D), Admission_Date); -- Adoptions CREATE TABLE Adoptions ( Name VARCHAR(20) NOT NULL, Species VARCHAR(10) NOT NULL, CONSTRAINT FK_Adoptions__Animals FOREIGN KEY (Name, Species) REFERENCES Animals (Name, Species) ON UPDATE CASCADE ON DELETE NO ACTION, Adopter_Email VARCHAR(100) NOT NULL REFERENCES Persons (Email) ON UPDATE CASCADE ON DELETE NO ACTION, PRIMARY KEY (Name, Species, Adopter_Email), -- An animal may be adopted only once by the same person (allows for future implementation of adoption returns) Adoption_Date DATE NOT NULL, Adoption_Fee SMALLINT NOT NULL CHECK (Adoption_Fee >= 0), INDEX NCIDX_FK_Adoptions__Persons (Adopter_Email), ); INSERT INTO Adoptions (Name, Species, Adopter_Email, Adoption_Date, Adoption_Fee) SELECT A.Name, A.Species, Adopter.Email, Adoption.Date, dbo.Random(@Min_Adoption_Fee, @Max_Adoption_fee) FROM Animals AS A CROSS APPLY ( SELECT C.Date FROM Reference.Calendar AS C WHERE C.Date > A.Admission_Date AND C.Date < @Last_Data_Day ORDER BY NEWID() OFFSET 0 ROWS FETCH NEXT 1 ROW ONLY ) AS Adoption(Date) CROSS APPLY ( SELECT Email FROM Persons WHERE Adoption.Date IS NOT NULL -- dummy reference to force row execution ORDER BY NEWID() OFFSET 0 ROWS FETCH NEXT 1 ROW ONLY ) AS Adopter ORDER BY NEWID() OFFSET 0 ROWS FETCH NEXT @Num_Adoptions ROWS ONLY; /* -- Future optional -- Animal routine checkups CREATE TABLE Routine_Checkups ( Name VARCHAR(20) NOT NULL, Species VARCHAR(10) NOT NULL, CONSTRAINT FK_Routine_Checkups__Animals FOREIGN KEY (Name, Species) REFERENCES Animals (Name, Species), Checkup_Time DATETIME2 NOT NULL, Temperature_F DECIMAL(4, 1) NOT NULL, Heart_Rate TINYINT NOT NULL, Respiration TINYINT NOT NULL, Weight_Lbs DECIMAL(4, 1) NOT NULL, Comments VARCHAR(500) NULL, Performed_By VARCHAR(100) NOT NULL REFERENCES Staff (Email) ON UPDATE CASCADE ON DELETE NO ACTION, PRIMARY KEY (Name, Species, Checkup_Time), INDEX NCIDX_FK_Routine_Checkups__Staff(Performed_By) ); INSERT INTO Routine_Checkups (Name, Species, Checkup_Time, Temperature_F, Heart_Rate, Respiration, Weight_Lbs, Comments, Performed_By) SELECT A.Name, A.Species, DATEADD(MINUTE, (7 * 60) + dbo.Random(0, (8 * 60)), CAST(C.Date AS DATETIME2)) AS Checkup_Time, ROUND( (SNR.Temperature_Low + ((SNR.Temperature_High - SNR.Temperature_Low) / 2.00)) -- Middle of range + ((dbo.Random(-10, 10) / 30.00) * (SNR.Temperature_High - SNR.Temperature_Low)) -- +/- 30% around the middle of the range + CASE WHEN dbo.Random(1, 100) < 100 -- 1 in 100 rows give abnormal reading THEN 0 ELSE (dbo.Random(-10, 10) / 30.00) * (SNR.Temperature_High - SNR.Temperature_Low) -- by +/- 1/3 of range END, 1 ) AS Temperature, ROUND( (SNR.Heart_Rate_Low + ((SNR.Heart_Rate_high - SNR.Heart_Rate_Low) / 2.00)) -- Middle of range + ((dbo.Random(-10, 10) / 30.00) * (SNR.Heart_Rate_high - SNR.Heart_Rate_Low)) -- +/- 30% around the middle of the range + CASE WHEN dbo.Random(1, 100) < 100 -- 1 in 100 rows give abnormal reading THEN 0 ELSE (dbo.Random(-10, 10) / 30.00) * (SNR.Heart_Rate_high - SNR.Heart_Rate_Low) -- by +/- 1/3 of range END, 0 ) AS Heart_Rate, ROUND( (SNR.Respiratory_Rate_Low + ((SNR.Respiratory_Rate_High - SNR.Respiratory_Rate_Low) / 2.00)) -- Middle of range + ((dbo.Random(-10, 10) / 30.00) * (SNR.Respiratory_Rate_High - SNR.Respiratory_Rate_Low)) -- +/- 30% around the middle of the range + CASE WHEN dbo.Random(1, 100) < 100 -- 1 in 100 rows give abnormal reading THEN 0 ELSE (dbo.Random(-10, 10) / 30.00) * (SNR.Respiratory_Rate_High - SNR.Respiratory_Rate_Low) -- by +/- 1/3 of range END, 0 ) AS Respistory_Rate, ROUND( CASE A.Species WHEN 'Cat' THEN 6.00 + ((ABS(CHECKSUM(A.Breed)) % 24) / 2) -- consistent base weight / breed + ((CASE WHEN dbo.Random(1, 10) < 10 THEN 0 ELSE 1 END) * (dbo.Random(-10, 10) / 20.00)) -- cats (6 - 17 lbs) +/- 0.5 lbs 1 in 10 WHEN 'Dog' THEN 15.00 + ((ABS(CHECKSUM(A.Breed)) % 72) / 2) + ((CASE WHEN dbo.Random(1, 10) < 10 THEN 0 ELSE 1 END) * (dbo.Random(-15, 15) / 10.00)) -- dogs (15 - 50 lbs) + / 1.5 lbs 1 in 10 ELSE 2.00 + (ABS(CHECKSUM(A.Breed) % 21)) / 3 + ((CASE WHEN dbo.Random(1, 20) < 20 THEN 0 ELSE 1 END) * (dbo.Random(-10, 10) / 50.00)) -- rabbits (2 - 8 lbs) +/ 0.2 lbs 1 in 20 END, 1 ) AS Weight, NULL AS Comments, Staff.Email FROM ( Animals AS A LEFT OUTER JOIN Adoptions AS AD ON A.Name = AD.Name AND A.Species = AD.Species ) INNER JOIN Reference.Species_Vital_Signs_Ranges AS SNR ON A.Species = SNR.Species CROSS JOIN Reference.Calendar AS C CROSS APPLY ( SELECT Email FROM Staff WHERE C.Date >= Staff.Hire_Date AND Email IN (SELECT Email FROM Staff_Assignments WHERE Role IN ('Veterinarian', 'Assistant')) -- Dirty shortcut but Emails are unique... ORDER BY NEWID() OFFSET 0 ROWS FETCH NEXT 1 ROW ONLY ) AS Staff WHERE C.Date BETWEEN @Shelter_Open AND @Last_Data_Day AND C.Weekday BETWEEN 2 AND 6 AND C.US_Federal_Holiday IS NULL AND (ABS(CHECKSUM(A.Name + A.Species)) % 5) + 2 = C.Weekday -- every animal checked weekly AND C.Date >= A.Admission_Date -- only post admission AND C.Date <= ISNULL(AD.Adoption_Date, @Last_Data_Day) -- only pre-adoption AND ABS(CHECKSUM(A.Name + A.Species + CAST(C.Date AS VARCHAR(20))) % 100) > 74; -- only 1/4 of all possible combinations */ CREATE TABLE Vaccinations ( Name VARCHAR(20) NOT NULL, Species VARCHAR(10) NOT NULL, CONSTRAINT FK_Vaccinations__Animals FOREIGN KEY (Name, Species) REFERENCES Animals (Name, Species), Vaccination_Time DATETIME2 NOT NULL, Vaccine VARCHAR(50) NOT NULL, CONSTRAINT FK_Vaccinations__Species_Vaccines FOREIGN KEY (Species, Vaccine) REFERENCES Reference.Species_Vaccines (Species, Vaccine) ON UPDATE CASCADE ON DELETE NO ACTION, Batch VARCHAR(20) NOT NULL, Comments VARCHAR(500) NULL, Email VARCHAR(100) NOT NULL REFERENCES Staff (Email) ON UPDATE CASCADE ON DELETE NO ACTION, PRIMARY KEY (Name, Species, Vaccine, Vaccination_Time), INDEX NCIDX_FK_Vaccinations__Vaccines(Species, Vaccine), INDEX NCIDX_FK_Vaccinations__Staff(Email) ); INSERT INTO Vaccinations (Name, Species, Vaccination_Time, Vaccine, Batch, Comments, Email) SELECT A.Name, A.Species, DATEADD(MINUTE, (7 * 60) + dbo.Random(0, (8 * 60)), CAST(C.Date AS DATETIME2)) AS Vaccination_Time, V.Vaccine, CHAR(dbo.Random(ASCII('A'), ASCII('Z'))) + '-' + CAST(dbo.Random(500000000, 99000000) AS CHAR(9)), NULL, Staff.Email FROM ( Animals AS A LEFT OUTER JOIN Adoptions AS AD ON A.Name = AD.Name AND A.Species = AD.Species ) INNER JOIN Reference.Species_Vaccines AS V ON V.Species = A.Species CROSS JOIN Reference.Calendar AS C CROSS APPLY ( SELECT Email FROM Staff WHERE C.Date >= Staff.Hire_Date -- After hire date AND A.name IS NOT NULL AND V.Vaccine IS NOT NULL AND Email IN (SELECT Email FROM Staff_Assignments WHERE Role IN ('Veterinarian', 'Assistant')) -- Dirty shortcut but Emails are unique... ORDER BY NEWID() OFFSET 0 ROWS FETCH NEXT 1 ROW ONLY ) AS Staff WHERE V.Species = A.Species AND C.Date BETWEEN @Shelter_Open AND @Last_Data_Day AND (ABS(CHECKSUM(A.Species + A.Name)) % 365) + 1 = C.Day_of_Year -- every animal vaccinated annualy AND C.Weekday BETWEEN 2 AND 6 -- on weekdays only AND C.US_Federal_Holiday IS NULL -- not on holidays AND C.Date >= A.Admission_Date -- only post admission AND C.Date <= ISNULL(AD.Adoption_Date, @Last_Data_Day) -- only pre-adoption AND 1 = CASE WHEN DATEDIFF(WEEK, A.Birth_Date, C.Date) NOT BETWEEN 14 AND 16 -- Parvo between 14 and 16 weeks only AND V.Vaccine = 'Parvovirus' THEN 0 ELSE 1 END AND ABS(CHECKSUM(A.Name + V.Vaccine + CAST(C.Date AS VARCHAR(20))) % 100) > 66 -- only 1/3 of all possible combinations ----------------------- -- Sanity check data -- ----------------------- SELECT 'Persons' AS Table_Name, * FROM Persons ORDER BY Email OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY; SELECT 'Staff' AS Table_Name, * FROM Staff ORDER BY Email OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY; SELECT 'Staff_Assignments' AS Table_Name, * FROM Staff_Assignments ORDER BY Email OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY; SELECT 'Animals' AS Table_Name, * FROM Animals ORDER BY Species, Breed, Name OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY; SELECT 'Adoptions' AS Table_Name, * FROM Adoptions ORDER BY Adoption_Date OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY; --SELECT 'Routine_Checkups' AS Table_Name, -- * --FROM Routine_Checkups --ORDER BY Checkup_Time OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY; SELECT 'Vaccinations' AS Table_Name, * FROM Vaccinations ORDER BY Vaccination_Time OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY; SELECT COUNT(*) AS Num_Persons, MIN(Birth_Date) AS Min_Birth_Date, MAX(Birth_Date) AS Max_Birth_Date FROM Persons; SELECT COUNT(*) AS Num_Staff, MIN(Hire_Date) AS Min_Hire_Date, MAX(Hire_Date) AS Max_Hire_Date FROM Staff; SELECT COUNT(*) AS Num_Assignments, MIN(Assigned) AS Min_A, MAX(Assigned) AS Max_A FROM Staff_Assignments; SELECT Species, Breed, COUNT(*) AS Num_Animals FROM Animals GROUP BY GROUPING SETS (Species), (Breed) ORDER BY Species, Breed, Num_Animals DESC; --SELECT COUNT(*) AS Total_Routine_Checkups --FROM Routine_Checkups; SELECT COUNT(*) AS Total_Vaccinations FROM Vaccinations AS V SELECT COUNT(*) AS Num_Adoptions, MIN(Adoption_Date) AS Min_Adoption_Date, MAX(Adoption_Date) AS Max_Adoption_Date FROM Adoptions; SELECT Adopter_Email, COUNT(*) AS Num_Adopted FROM Adoptions GROUP BY Adopter_Email; --SELECT A.Species, -- COUNT(*) AS Num_Rows, -- MIN(RC.Temperature_F) AS Min_Temp, -- MAX(RC.Temperature_F) AS Max_Temp, -- AVG(RC.Temperature_F) AS Avg_Temp, -- MIN(RC.Heart_Rate) AS Min_Heart, -- MAX(RC.Heart_Rate) AS Max_Heart, -- AVG(RC.Heart_Rate) AS Avg_Heart, -- MIN(RC.Respiration) AS Min_Resp, -- MAX(RC.Respiration) AS Max_Resp, -- AVG(RC.Respiration) AS Avg_Resp, -- MIN(RC.Weight_Lbs) AS Min_Weight, -- MAX(RC.Weight_Lbs) AS Max_Weight, -- AVG(RC.Weight_Lbs) AS Avg_Weight --FROM Routine_Checkups AS RC -- INNER JOIN -- Animals AS A -- ON A.Name = RC.Name -- AND -- A.Species = RC.Species --GROUP BY A.Species; COMMIT TRANSACTION;