Best way to return results that are between two specific dates (dynamic, and fixed e.g. this calendar month, last calendar month, this annum)

Wappler General Need Help
1

I have a basic ‘transaction’ type DB, whenever a user actions a certain thing - I input:
user_id
org_id
number (integer)
date (datetime).

What I’d like to be able to do, is the following:

Return the number sum of ‘number’ for:

  • this month
  • last month
  • this year
  • dynamically between two dates of the users choosing

Perhaps this is one of those use cases for ‘views’ which I haven’t explored yet, however, I’m seemingly unable to do this within the query builder, and even repeating using a where or outputting to a set value so I can use formatters doesn’t really give me the option, either.

I have an idea that I could somehow pass the appropriate dates from the F/E to the B/E - which is what I’m exploring now. The date formatters on SC just don’t seem like they are able to handle this natively, and so I might need a custom formatter of some kind?

I’d prefer to try and process this on the BE.

2

I am not sure what setup you have right now for this, but this is how I would do something like this:

  1. Send a flag from client side stating the type (say date_filter) along with start & end dates.
  2. On SA, create two variables startDate & endDate and set the values as GET or POST param with the same name.
  3. Then, use conditions to identify if date_filter is not of dynamic type.
  4. Based on this type, use NOW & date formatters (like add day, add month etc) to create the start and end dates for this month, last month & this year. Or, you can just create a custom formatter and move the logic there and return start/end dates.
  5. Based on calculations in 4, set the two start/end date variables with the calculated values.

Finally, the query you write can use these two dates as conditions.