Query to group by month starting on arbitrary day of the month

[Oon-Ee Ng]

Currently grouping by month (or year) gives primacy to the first day of the month/year as the cut-off point. Is there a way to arbitrarily select a different cut-off point?

So for example my credit card statement may get generated on the 15th of each month. For a particular card I may need to know whether my spending in a particular statement month (which starts on the 15th and ends on the 14th of the next month) has hit a certain amount (for rewards, or to fulfill certain requirements).

Ideally I would be able to:-

SELECT

    year, month, account, sum(cost(position))

WHERE

    account ~ 'Liabilities:CreditCard:InstituteA:CardName'

GROUP BY year, MONTH(15)

or something of that sort.

[Oon-Ee Ng]

Currently the (convoluted) method I use based on my SQL experience works like this:-

SELECT

    account, sum(cost(position))

WHERE

    date > DATE(YEAR(today()), MONTH(DATE_ADD(DATE(YEAR(today()), MONTH(today()), 1), -1)), 15)

    and account ~ 'Liabilities:CreditCard:InstituteA:CardName'

GROUP BY account

 

This works in a pinch, as I'm really normally only interested in the most recent statement. However this doesn't allow me to track statement amounts over time in a graph, for example. Any suggestions would be deeply appreciated.