Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
An hours database
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.