In SQL I am trying to Create an inline table function. I will give you my script first then the instructions.

CREATE DATABASE Computer_Inventory_System

CREATE TABLE Brands ( BrandKey int IDENTITY(1,1) PRIMARY KEY NOT NULL, Brand varchar(40) NOT NULL, Active bit DEFAULT(1) NOT NULL )

CREATE TABLE ComputerTypes ( ComputerTypeKey int IDENTITY(1,1) PRIMARY KEY NOT NULL, ComputerType varchar(25) NOT NULL )

CREATE TABLE ComputerStatuses ( ComputerStatusKey int IDENTITY(1,1) PRIMARY KEY NOT NULL, ComputerStatus varchar(50) NOT NULL, ActiveStatus bit NOT NULL --an indicator of if this status means the computer is available or not )

CREATE TABLE CPUTypes ( CPUTypeKey int IDENTITY(1,1) PRIMARY KEY NOT NULL, CPUType varchar(40) NOT NULL )

CREATE TABLE Computers ( ComputerKey int IDENTITY(1,1) PRIMARY KEY NOT NULL, ComputerTypeKey int NOT NULL, BrandKey int NOT NULL, ComputerStatusKey int NOT NULL DEFAULT(0), PurchaseDate date NOT NULL, PurchaseCost money NOT NULL, MemoryCapacityInGB int NOT NULL, HardDriveCapacityinGB int NOT NULL, VideoCardDescription varchar (255), CPUTypeKey int NOT NULL, CPUClockRateInGHZ decimal (6, 4) )

SET IDENTITY_INSERT Computers ON INSERT Computers (ComputerKey, ComputerTypeKey, BrandKey, ComputerStatusKey, PurchaseDate, PurchaseCost, MemoryCapacityInGB, HardDriveCapacityinGB, VideoCardDescription, CPUTypeKey, CPUClockRateInGHZ) VALUES (1, 1, 1, 0, '1/1/2017', 1999.99, 32, 1024, 'Nvidia 1080', 1, 3.5), (2, 2, 4, 0, '1/1/2017', 2399.99, 16, 512, 'Nvidia GeForce GT 650M', 1, 2.5) SET IDENTITY_INSERT Computers OFF

CREATE TABLE Departments ( DepartmentKey int IDENTITY(1,1) PRIMARY KEY NOT NULL, Department varchar(255) )

SET IDENTITY_INSERT Departments ON INSERT Departments (DepartmentKey, Department) VALUES (1, 'CEO'), (2, 'Human Resources'), (3, 'Information Technology'), (4, 'Accounting') SET IDENTITY_INSERT Departments OFF

CREATE TABLE Employees ( EmployeeKey int IDENTITY(1,1) PRIMARY KEY NOT NULL, LastName varchar(25) NOT NULL, FirstName varchar(25) NOT NULL, Email varchar(50) NOT NULL, Hired date NOT NULL, Terminated date NULL, DepartmentKey int NOT NULL, SupervisorEmployeeKey int NOT NULL --CEO/Top of hierarchy should have their own EmployeeKey )

SET IDENTITY_INSERT Employees ON INSERT Employees (EmployeeKey, LastName, FirstName, Email, Hired, DepartmentKey, SupervisorEmployeeKey) VALUES (1, 'Ceo', 'John The', 'JCeo@thiscompany.com', '1/1/2017', 1, 1), (2, 'Brother', 'Big', 'BBrother@thiscompany.com', '1/1/2017', 2, 1), (3, 'Geek', 'Major', 'MGeek@thiscompany.com', '1/1/2017', 3, 1) SET IDENTITY_INSERT Employees OFF

CREATE TABLE EmployeeComputers ( EmployeeComputerKey int IDENTITY(1,1) PRIMARY KEY NOT NULL, EmployeeKey int NOT NULL, ComputerKey int NOT NULL, Assigned date NOT NULL, Returned date NULL )

SET IDENTITY_INSERT ComputerStatuses ON INSERT ComputerStatuses (ComputerStatusKey, ComputerStatus, ActiveStatus) VALUES (0, 'New', 1), (1, 'Assigned', 1), (2, 'Available', 1), (3, 'Lost', 0), (4, 'In for Repairs', 0), (5, 'Retired', 1) SET IDENTITY_INSERT ComputerStatuses OFF

SET IDENTITY_INSERT CPUTypes ON INSERT CPUTypes (CPUTypeKey, CPUType) VALUES (1, 'AMD'), (2, 'Intel'), (3, 'Samsung'), (4, 'Apple'), (5, 'Qualcomm') SET IDENTITY_INSERT CPUTypes OFF

SET IDENTITY_INSERT ComputerTypes ON INSERT ComputerTypes (ComputerTypeKey, ComputerType) VALUES (1, 'Desktop'), (2, 'Laptop'), (3, 'Tablet'), (4, 'Phone') SET IDENTITY_INSERT ComputerTypes OFF

SET IDENTITY_INSERT Brands ON INSERT Brands (BrandKey, Brand) VALUES (1, 'Apple'), (2, 'Samsung'), (3, 'Sony'), (4, 'HP'), (5, 'Acer'), (6, 'NVidia') SET IDENTITY_INSERT Brands OFF

ALTER TABLE Computers ADD CONSTRAINT FK_ComputerComputerTypes FOREIGN KEY (ComputerTypeKey) REFERENCES ComputerTypes (ComputerTypeKey)

ALTER TABLE Computers ADD CONSTRAINT FK_ComputerBrands FOREIGN KEY (BrandKey) REFERENCES Brands (BrandKey)

ALTER TABLE Computers ADD CONSTRAINT FK_ComputerComputerStatus FOREIGN KEY (ComputerStatusKey) REFERENCES ComputerStatuses (ComputerStatusKey)

ALTER TABLE Computers ADD CONSTRAINT FK_ComputerCPUType FOREIGN KEY (CPUTypeKey) REFERENCES CPUTypes (CPUTypeKey)

ALTER TABLE Employees ADD CONSTRAINT FK_EmployeeDepartment FOREIGN KEY (DepartmentKey) REFERENCES Departments (DepartmentKey)

ALTER TABLE Employees ADD CONSTRAINT FK_EmployeeSupervisor FOREIGN KEY (SupervisorEmployeeKey) REFERENCES Employees (EmployeeKey)

ALTER TABLE EmployeeComputers ADD CONSTRAINT FK_EmployeeComputerEmployee FOREIGN KEY (EmployeeKey) REFERENCES Employees (EmployeeKey)

ALTER TABLE EmployeeComputers ADD CONSTRAINT FK_EmployeeComputerComputer FOREIGN KEY (ComputerKey) REFERENCES Computers (ComputerKey);

CREATE TABLE ComputerAssignments ( ComputerAssignmentKey int IDENTITY(1,1) PRIMARY KEY NOT NULL, ComputerStatusKey int NOT NULL, Added bit DEFAULT(1) NOT NULL, Assigned bit DEFAULT(1) NOT NULL, Reassigned bit DEFAULT(1) NOT NULL, Lost bit DEFAULT(1) NOT NULL, StartDate datetime NOT NULL );

SET IDENTITY_INSERT ComputerAssignments ON INSERT ComputerAssignments (ComputerAssignmentKey, ComputerStatusKey, Added, Assigned, Reassigned, Lost, StartDate) VALUES (0, 0, 1, 1, 0, 0, '1/1/2017'), (1, 1, 0, 1, 0, 0, '1/13/2017'), (2, 2, 1, 0, 0, 0, '2/1/2017'), (3, 3, 0, 0, 0, 1, '1/13/2017'), (4, 4, 0, 1, 0, 0, '5/8/2017'), (5, 5, 0, 0, 0, 0, '6/10/2017')

SET IDENTITY_INSERT ComputerAssignments OFF

ALTER TABLE ComputerAssignments ADD CONSTRAINT FK_ComputerStatusKeyAssignment FOREIGN KEY (ComputerStatusKey) REFERENCES ComputerStatuses (ComputerStatusKey)

SET IDENTITY_INSERT EmployeeComputers ON INSERT EmployeeComputers (EmployeeComputerKey, EmployeeKey, ComputerKey, Assigned, Returned) VALUES (1,1,1, '1/1/2017', NULL), (2,2,2, '2/1/2017', NULL), (3,3,2, '4/18/2017', '07/22/2017'), (4,3,1, '3/18/2017', NULL), (5,3,1, '4/10/2017', NULL), (6,2,1, '1/13/2017', '05/12/2017'), (7,1,1, '10/13/2017', NULL), (8,1,2, '10/13/2017', NULL), (9,1,1, '11/03/2016', '02/02/2017')

SET IDENTITY_INSERT EmployeeComputers OFF

-- run this part by itself CREATE VIEW ComputersForRepair AS

SELECT B.Brand, CT.ComputerType, CS.ComputerStatus, (E.LastName + ', ' + E.FirstName) AS Employee, E.Email, DATEDIFF(dd,EC.Assigned,GETDATE()) as DaysforRepair, C.MemoryCapacityInGB, C.HardDriveCapacityinGB FROM Computers C INNER JOIN Brands B ON C.BrandKey = B.BrandKey INNER JOIN ComputerTypes CT ON CT.ComputerTypeKey = C.ComputerTypeKey INNER JOIN ComputerStatuses CS ON CS.ComputerStatusKey = C.ComputerStatusKey INNER JOIN EmployeeComputers EC ON EC.ComputerKey = C.ComputerKey INNER JOIN Employees E ON E.EmployeeKey = EC.EmployeeKey WHERE CT.ComputerTypeKey = '4'

Assign one of the new machines to the CEO, then return it and assign the other machine (the CEO hated your first choice), Try to retire the second machine you assigned to the CEO (he's picky...)

I am not really sure what else you need. The CEO is "John The CEO". There are only 3 employees so far in the database. This query should help you to see what the new computers are. I guess you could add a few computers if that makes it easier.

SELECT DISTINCT

C.ComputerKey AS ComputerNumber, CS.ComputerStatus, B.Brand, C.CPUClockRateInGHZ,

CPT.CPUType, CT.ComputerType, C.MemoryCapacityInGB,

CAST(C.HardDriveCapacityinGB AS float) / CAST(1000 AS float) AS HardDriveCapacityinTB, C.VideoCardDescription,

E.LastName + ' ' + E.FirstName As EmployeeName

FROM ComputerStatuses CS

INNER JOIN ComputerAssignments CA

ON CS.ComputerStatusKey = CA.ComputerStatusKey

INNER JOIN Computers C

ON CA.ComputerStatusKey = C.ComputerStatusKey

INNER JOIN Brands B

ON C.BrandKey = B.BrandKey

INNER JOIN ComputerTypes CT

ON C.ComputerTypeKey = CT.ComputerTypeKey

INNER JOIN CPUTypes CPT

ON C.CPUTypeKey = CPT.CPUTypeKey

INNER JOIN (SELECT MAX(Assigned) AS RecentAssignedDate,

ComputerKey FROM EmployeeComputers Group By ComputerKey) AS RAC --RecentelyAssingedComputer

ON RAC.ComputerKey = C.ComputerKey

INNER JOIN EmployeeComputers EC

--here it filters the employee recently assigned to computer

ON C.ComputerKey = EC.ComputerKey AND EC.Assigned = RAC.RecentAssignedDate

INNER JOIN Employees E

ON EC.EmployeeKey = E.EmployeeKey

WHERE CS.ComputerStatusKey = 0 OR CS.ComputerStatusKey = 2

AND EC.Assigned IS NOT NULL

I think that is basically what I am asking you. Nevermind, I will figure it out myself.