Report request: recurring expenses report

[MlinMMEX]

Hello,
I would like to get a report of my recurring expenses, (most frequent), with their amount, period (weekly, monthly, etc) and amount paid YTD.
It would be great to have filters, as it might be that investments commissions, or weekly contributions might appear...

I have no clue how to start...

[guangong]

if you already added an empty or an sample report, the next step would be query the recurring tables

Code: Select all

 select * from BILLSDEPOSITS_V1;

here is the table schema

Code: Select all

-- Describe BILLSDEPOSITS_V1
CREATE TABLE BILLSDEPOSITS_V1(
BDID integer primary key 
, ACCOUNTID integer NOT NULL
, TOACCOUNTID integer
, PAYEEID integer NOT NULL
, TRANSCODE TEXT NOT NULL /* Withdrawal, Deposit, Transfer */
, TRANSAMOUNT numeric NOT NULL
, STATUS TEXT /* None, Reconciled, Void, Follow up, Duplicate */
, TRANSACTIONNUMBER TEXT
, NOTES TEXT
, CATEGID integer
, TRANSDATE TEXT
, FOLLOWUPID integer
, TOTRANSAMOUNT numeric
, REPEATS integer
, NEXTOCCURRENCEDATE TEXT
, NUMOCCURRENCES integer
, COLOR integer DEFAULT -1
);
CREATE INDEX IDX_BILLSDEPOSITS_ACCOUNT ON BILLSDEPOSITS_V1 (ACCOUNTID, TOACCOUNTID);

[MlinMMEX]

Thank you for your help... Seems that the table BILLSDEPOSITS_V1 is empty, as there are no rows in any of my files.

So this table will have calculated the number of repetitions of the most frequent transactions in the past? Which field REPEATS, or NUMBEROFOCURRENCES?

[guangong]

It is about recurring instead of.

Code: Select all

    enum REPEAT_TYPE {
        REPEAT_ONCE = 0,
        REPEAT_WEEKLY,
        REPEAT_BI_WEEKLY,      // FORTNIGHTLY
        REPEAT_MONTHLY,
        REPEAT_BI_MONTHLY,
        REPEAT_QUARTERLY,      // TRI_MONTHLY
        REPEAT_HALF_YEARLY,
        REPEAT_YEARLY,
        REPEAT_FOUR_MONTHLY,   // QUAD_MONTHLY
        REPEAT_FOUR_WEEKLY,    // QUAD_WEEKLY
        REPEAT_DAILY,
        REPEAT_IN_X_DAYS,
        REPEAT_IN_X_MONTHS,
        REPEAT_EVERY_X_DAYS,
        REPEAT_EVERY_X_MONTHS,
        REPEAT_MONTHLY_LAST_DAY,
        REPEAT_MONTHLY_LAST_BUSINESS_DAY
    }; 

[MlinMMEX]

Hello,
Table BILLSDEPOSITS_V1 already exists... Why do you create it again?
What is the enum type REPEAT_TYPE goal? Where should it be placed?