Just to have a way to see my progress at Udemy, I created the following table and view: CREATE TABLE rawSummaryUdemy ( date TEXT NOT NULL DEFAULT CURRENT_DATE, total INTEGER NOT NULL, completed INTEGER NOT NULL,
CONSTRAINT formatDate CHECK(date = date(strftime('%s', date), 'unixepoch')), CONSTRAINT notInFuture CHECK(date <= date()), CONSTRAINT totalIsInt CHECK(TYPEOF(total) = 'integer'), CONSTRAINT completedIsInt CHECK(TYPEOF(completed) = 'integer'), CONSTRAINT totalGEZero CHECK(total >= 0), CONSTRAINT completedGEZero CHECK(completed >= 0), CONSTRAINT completedLETotal CHECK(completed <= total), PRIMARY KEY(date) ); CREATE VIEW summaryUdemy AS SELECT * , total - completed AS toComplete FROM rawSummaryUdemy ; If this can be done better: let me know. Normally speaking total and completed should never decrease. It is not really important, but just as an exercise: is it possible to add constraints so that you cannot enter a total, or a completed that is lower as the previous one? -- Cecil Westerhof _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users