An hours database

hours-database.sql

BEGIN TRANSACTION;

CREATE TABLE WorkSession (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  jobId      INTEGER,
  date       DATE                DEFAULT CURRENT_DATE,
  startTime  VARCHAR(10),
  endTime    VARCHAR(10),
  thingsDone VARCHAR(4096),
  FOREIGN KEY (jobId) REFERENCES JobPositions (id)
);

CREATE TABLE PayRate (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  jobId         INTEGER             DEFAULT 1,
  dateEffective DATE                DEFAULT CURRENT_DATE,
  payRate       DOUBLE PRECISION,
  FOREIGN KEY (jobId) REFERENCES JobPositions (id)
);

CREATE TABLE JobPositions (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  title       VARCHAR(45),
  description VARCHAR(500)
);

CREATE VIEW vwWorkHours AS
SELECT
		*,
		(strftime('%s',endDate) - strftime('%s',startDate)) / 3600.0 as Hours
	FROM
		(SELECT
			id,
      jobId,
			date,
			startTime,
			endTime,
			date || ' ' || startTime as startDate,
			date || ' ' || endTime as endDate
		FROM
			WorkSession);

CREATE VIEW vwWeekPay AS
SELECT
	strftime('%Y-%W', date),
	count(*) as sessions,
	sum(hours) as weekHours, 
	sum(sessPay) as weekPay
FROM vwSessionPay
GROUP BY strftime('%Y-%W', date);

CREATE VIEW vwSessionPayRate AS
SELECT id, jobId, date, startTime, endTime, hours,
	(SELECT payRate 
		FROM PayRate pr 
		WHERE pr.jobId = wh.jobId 
		AND strftime('%s', pr.dateEffective) < strftime('%s', wh.date)
		ORDER BY strftime('%s', pr.dateEffective) DESC
		LIMIT 1) as sessPayRate
FROM vwWorkHours as wh;

CREATE VIEW vwSessionPay AS
SELECT *,
	   hours * sessPayRate as sessPay
 FROM vwSessionPayRate
 WHERE hours > 0;

CREATE VIEW vwMonthPay AS
SELECT
	strftime('%Y-%m', date),
	count(*) as sessions,
	sum(hours) as monthHours, 
	sum(sessPay) as monthPay
FROM vwSessionPay
GROUP BY strftime('%Y-%m', date);

CREATE VIEW vwDayPay AS
SELECT
	date,
	count(*) as sessions,
	sum(hours) as dayHours, 
	sum(sessPay) as dayPay
FROM vwSessionPay
GROUP BY date;

COMMIT;