Part 03 Notes

[tuf42483]

(Primary Key, Foreign Key)

CUSTOMERS: CustomerID, CustFirstName, CustLastName, CustAddress, CustPhone

EMPLOYEES: EmployeeID, EmployeeFirstName, EmployeeLastName, EmployeeAddress, EmployeePhone, EmployeeType (1, 2), EmployeeDesc (1 = Technician, 2 = Agent)

HIRING: EmployeeID, StartDate, EndDate

LOCATION: LocationID (1, 2, 3), LocDesc (1 = store, 2 = residence/house, 3 = office)

DURATION: JobID, EmployeeID, StartTime, EndTime, Duration

SCHEDULING: ScheduleID, EmployeeID, LocationID, CustomerID, CustFirstName, CustLastName, CustAddress, CustPhone, Date, Time, JobID

INVOICES: InvoiceID, CustomerID, JobID, InvoiceNum, InvoiceTotal, PaymentTotal, InvoiceDueDate, PaymentDate

MILEAGE: EmployeeID, StartMileage, EndMileage, Date, TotalMileage ( = EndMileage - StartMileage)

COMMISSION: EmployeeID, JobID, DurationTotal (StartTime - EndTime), TotalMileage, PayPerMile ($), PayPerHour ($), Commission ( = (DurationTotal * PayPerHour) + (TotalMileage * PayPerMile))

PRODUCTS: ProductID, ProductName (x wire, x computer, x server), ProductType(1, 2, ... , n), ProductPrice ($), ProductStatus ("In Stock", "Sold Out")

ORDERS: OrderID, CustomerID, OrderDate, ShippedDate

ORDER_DETAILS: OrderID, ProductID, OrderQty

INVENTORY: ProductID, ProductQty (HAVING ProductStatus = "In Stock")

SALES: NumProductsSold, SalesTotal ($)

[kennyle32]

Looks good, for the PKs and FKs we can make sure they're good as we start to create the ER diagrams with 1:M, M;N etc.

[tuf42483]

Yeah, the PKs and FKs aren't exact or accurate yet, just possibilities. We'll probably need to add more of each.

[tuf42483]

Calculating Duration

CREATE TABLE Duration ( JobID INT PRIMARY KEY, StartDate DATETIME NOT NULL, EndDate DATETIME NOT NULL );

INSERT INTO Duration (StartDate, EndDate) VALUES( '2016-01-01 6:34:12 AM', '2016-01-01 12:45:34 PM'); INSERT INTO Duration (StartDate, EndDate) VALUES( '2016-01-02 9:23:08 AM', '2016-01-02 5:05:37 PM'); INSERT INTO Duration (StartDate, EndDate) VALUES( '2016-01-03 4:34:12 PM', '2016-01-03 4:55:18 PM'); INSERT INTO Duration (StartDate, EndDate) VALUES( '2016-01-04 11:02:00 AM', '2016-01-04 2:53:21 PM'); INSERT INTO Duration (StartDate, EndDate) VALUES( '2016-01-05 7:52:55 AM', '2016-01-05 9:08:48 AM'); INSERT INTO Duration (StartDate, EndDate) VALUES( '2016-01-06 7:59:11 PM', '2016-01-07 1:23:11 AM'); INSERT INTO Duration (StartDate, EndDate) VALUES( '2016-01-07 3:12:23 AM', '2016-01-07 8:02:25 PM');

SELECT *, DateDiff(second, StartDate, EndDate) AS TotalSeconds FROM Duration;

SELECT JobID, TotalSeconds / 3600 AS Hours, (TotalSeconds % 3600) / 60 AS Minutes, TotalSeconds % 60 AS Seconds FROM ( SELECT EventID, DateDiff(second, StartDate, EndDate) AS TotalSeconds FROM Duration );