Hi!
I'm trying to get a report of the market value of my assets at the end
of each month:
SELECT year, month,
CONVERT(LAST(balance), 'USD', DATE_ADD(DATE(year, month+1,
1), -1)) AS market_value,
CONVERT(COST(LAST(balance)), 'USD', DATE_ADD(DATE(year, month+1,
1), -1)) AS book_value
WHERE account ~ '^Assets:'
GROUP BY year, month
This fails with
Syntax error near '1'
Replacing "month+1" with "month" (as an experiment, it won't produce
correct results), I'm getting
ERROR: Mixed aggregates and non-aggregates are not allowed.
I think technically this should work, as year and month are in the GROUP
BY clause.
I came up with the following sketchy workaround:
SELECT year, month,
CONVERT(LAST(balance), 'USD',
DATE_ADD(YMONTH(DATE_ADD(YMONTH(FIRST(date)), 31)), -1)) AS market_value,
CONVERT(COST(LAST(balance)), 'USD',
DATE_ADD(YMONTH(DATE_ADD(YMONTH(FIRST(date)), 31)), -1)) AS book_value
WHERE account ~ '^Assets:'
GROUP BY year, month
It uses YMONTH() to get the 1st of the given month and year, adds 31
days to get the next month, runs YMONTH() again to get the 1st of the
next month, and subtracts one day.
It won't work in all cases however, for example if the first transaction
of January is on January 31, because adding 31 days "skips over" February.
Is there a way to get the last day of a month with the current
beancount.query or the new beanquery module, and if not, can I add one? :)
MySQL for example has a LAST_DAY() function.
Cheers,
Andreas
--
You received this message because you are subscribed to the Google Groups
"Beancount" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/beancount/b56de533-7eb7-42c6-a25f-fe5cadbe25b4%40gerstmayr.me.
