Every week I need to determine the number of tasks I reviewed and approved in the prior week. I track the tasks in a database on my MacBook Pro laptop using the SQLite utility that is part of the OS X operating system - see Using SQLite. I have a database named "CRQ" that contains a table named "Tasks". The schema for the table is shown below; approval dates are stored as integers; SQLite does not have a storage class specifically for dates, instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values1.
$ sqlite3 ~/Documents/Work/CRQ/CRQ.db SQLite version 3.8.10.2 2015-05-20 18:17:19 Enter ".help" for usage hints. sqlite> .schema Tasks CREATE TABLE "Tasks" ( `CRQ` TEXT NOT NULL, `Task` TEXT NOT NULL, `URL` TEXT, `Received` INTEGER, `Reviewed` INTEGER, `Approved` INTEGER, `Notes` TEXT, `Summary` TEXT ); sqlite> .exit $
I can open the database and manually type the command to count the records with an approval date on or after a date I specify using an SQL command like the one below:
$ sqlite3 ~/Documents/Work/CRQ/CRQ.db SQLite version 3.8.10.2 2015-05-20 18:17:19 Enter ".help" for usage hints. sqlite> SELECT COUNT(*) FROM Tasks WHERE Approved >= '2017-02-01'; 11 sqlite>
[ More Info ]
