I have written the following SQLite query:
SELECT sum(amount) / (julianday('now', '+1 day') - julianday('2012-08-11')) * 14 AS 'Cost Per Fortnight'
FROM
(
SELECT "transaction".transamount AS amount
FROM checkingaccount_v1 AS "transaction"
JOIN Category_v1 AS category ON "transaction".CategID = category.CategID
JOIN SubCategory_V1 AS subcategory ON "transaction".SubCategID = subcategory.SubCategID
WHERE "transaction".transdate BETWEEN date('2012-08-11') AND date('now')
AND category.categname = 'Food'
AND subcategory.subcategname = 'Groceries'
UNION ALL
SELECT splittransaction.splittransamount AS amount
FROM splittransactions_v1 AS splittransaction
JOIN Category_v1 AS category ON splittransaction.CategID = category.CategID
JOIN SubCategory_V1 AS subcategory ON splittransaction.SubCategID = subcategory.SubCategID
JOIN checkingaccount_v1 AS "transaction" ON splittransaction.TransID = "transaction".TransID
WHERE "transaction".transdate BETWEEN date('2012-08-11') AND date('now')
AND category.categname = 'Food'
AND subcategory.subcategname = 'Groceries'
) AS costs
I would like to know how I can simplify it. The two queries that are UNION ALL
ed are quite similar. Some values, such as the literal date, are also duplicated.
SQLite doesn't appear to support functions, stored procedures or variables. For this particular query, I require SQLite and can't use another programming language (like Perl) to execute it, so please don't suggest other tools/technologies.
If anyone is curious, the query is for a MoneyManagerEx report.