expense report by day

[John Ralls]

On Apr 12, 2012, at 12:13 PM, Gabor BORGULYA wrote:

> Dear List,
> 
> I saw many emails answering other questions, but no answer to mine. I suspect, that this is difficult.
> 
> The solution I thought about was substituting the original tables of the SQLite database with views displaying only a specific day and to get the account summary report. Then change the target day and rerun.
> Would this work? Is there a less cumbersome solution? Maybe with an other software?
> 

There's no plain expense report to customize, unfortunately. You could write a custom report, but I guess your Scheme is about as good as mine, so let's stick with SQL. ;-)

I don't know that creating a view would really help, and you should be aware that Gnucash sometimes rewrites the whole database so you might have to regenerate the view from one session to the next.

This query should get you pretty close to what you're looking for:

SELECT substr(enter_date, 0, 9) AS date, name, SUM (value_num / value_denom) AS total
FROM splits AS s INNER JOIN transactions AS t ON s.tx_guid = t.guid INNER JOIN accounts AS a ON s.account_guid = a.guid
WHERE t.enter_date >= "20120401" 
AND t.enter_date <= "20120412"
AND a.account_type = "EXPENSE"
GROUP BY date, name


You'll probably want to wrap it in some sort of script to pretty up the output and turn the dates into variables.

Regards,
John Ralls