Running SQL commands from a text file using sqlite3

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.

SQLite Tutorial for beginners
SQLite Tutorial for beginners 1x1 px 

$ 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:

Learn C# and SQLite Programming from basic to advanced
Learn C# and SQLite Programming
from basic to advanced 1x1 px 

$ 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>

If I only want the number approved on a particular date, I can use Approved = rather than Approved >=.

sqlite> SELECT COUNT(*) FROM Tasks WHERE Approved = '2017-02-01';
9
sqlite>

To save a little time each week, I'd like to be able to save the command in a file and have sqlite3 execute the command from the file, so I don't need to retype it. You can store SQL commands in a text file and have sqlite3 read the commands stored in the file using redirection. E.g., I could put the command SELECT COUNT(*) FROM Tasks WHERE Approved >= '2017-02-01'; in a file named WeeklyApprovalsCount.txt and have sqlite3 run the command by entering the command below in a Terminal window.

$ sqlite3 ~/Documents/Work/CRQ/CRQ.db < ~/Documents/Work/CRQ/WeeklyApprovalsCount.txt
11
$

If I needed to run additional commands, I could add them as additional lines in the file.

But I don't want to have to edit the file each week to change the date. I can use CURRENT_DATE for today's date rather than specifying the date each week.

sqlite> SELECT COUNT(*) FROM Tasks WHERE Approved = CURRENT_DATE;
1
slquite>

But I need to subtract 7 days from the current date in order to look for records in the table approved within the last week. To do that, I can use the DATE function as shown below2.

sqlite> SELECT COUNT(*) FROM Tasks WHERE Approved >= date(CURRENT_DATE,'-7 day');
11
sqlite>

So I could put SELECT COUNT(*) FROM Tasks WHERE Approved >= DATE(CURRENT_DATE,'-7 day'); in the text file WeeklyApprovalsCount.txt, instead, and not have to change the date each week. I could also use 'now', instead of CURRENT_DATE.

sqlite> SELECT COUNT(*) FROM Tasks WHERE Approved >= date('now','-7 day');
11
sqlite>

References:

  1. Using SQLite
    Created: April 8, 2016
    MoonPoint Support
  2. Date Add & Subtract in SQlite
    Last edited: December 4, 2009
    Do Any Stuff