Subscriptions start and end dates for each user, # of subscriptions per company on specific dates
I've got a products table.
I've got a users tables with user name, company name and other fields .
Users subscribe to one or more products, so i've got a UsersProducts table which links Products and Users.
When a user subscribes to a product, I set the Enddate to '31/12/2030'. When the user cancel subscription, I change the EndDate.
I would like the report user to select a date in a calendar list (I will display latest day of each month in this list) and the user will get the number of subscribers per company for each product at that particular date.
I will use a Matrix to show this information, but I can't find how to correctly fiter per date and group datas per company
The only way I found was to pre-populate another table, which would be repopulated each night. I'm quite sure there is a more clever and dynamic way to do that but I've spend hours to find it.