How can I simplify this query?

Question

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 ALLed 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.

Answer 1

I've managed to simplify it a bit:

SELECT sum(amount) / (julianday('now', '+1 day') - julianday('2012-08-11')) * 14 AS 'Cost Per Fortnight'
FROM
(
    SELECT transdate, transamount AS amount, CategID, SubCategID
    FROM checkingaccount_v1 AS "transaction"

    UNION ALL

    SELECT transdate, splittransamount AS amount, splittransaction.CategID, splittransaction.SubCategID
    FROM splittransactions_v1 AS splittransaction
    JOIN checkingaccount_v1 AS "transaction" USING (TransID)
)
JOIN Category_v1 AS category USING (CategID)
JOIN SubCategory_V1 AS subcategory USING (SubCategID)
WHERE transdate BETWEEN date('2012-08-11') AND date('now')
    AND categname = 'Food'
    AND subcategname = 'Groceries'

Answer 2

Can you explain the purpose of this join in the 2nd half of your query?

JOIN checkingaccount_v1 AS "transaction" ON splittransaction.TransID = "transaction".TransID

I can't experiment against your data, but would a common table expression for the two different data sources work?

;WITH AccountTransactions AS
(
    SELECT  transamount AS amount, TransID, 0 As Split, 
            transdate, categname, subcategname
    FROM    checkingaccount_v1
    UNION ALL
    SELECT  splittransamount AS amount, TransID, 1 AS Split, 
            transdate, categname, subcategname
    FROM    splittransactions_v1
), TransactionData AS
(
    SELECT  amount, TransID, Split
    FROM    AccountTransactions
    JOIN    Category_v1 AS category ON AccountTransactions.CategID = category.CategID
    JOIN    SubCategory_V1 AS subcategory ON AccountTransactions.SubCategID = subcategory.SubCategID
    WHERE   transdate BETWEEN date('2012-08-11') AND date('now')
        AND categname = 'Food'
        AND subcategname = 'Groceries'
)
SELECT  sum(amount) / (julianday('now', '+1 day') - julianday('2012-08-11')) * 14 AS 'Cost Per Fortnight'
FROM    TransactionData