Hi
I need your help.
Assume that we have following model:
SET DateFormat='YYYY/MM/DD';
[users]:
Load
AutoNumberHash128(user_name) as user_id,
user_name,
Date(creation_date) as creation_date,
Date(removal_date) as removal_date
Inline
[user_name, creation_date, removal_date,
'USER A', '2016/01/01', '2020/05/01',
'USER B', '2019/01/01', '2020/02/01'
'USER C', '2017/04/01', '2018/12/01'
];
[history]:
Load
AutoNumberHash128(user_name) as user_id,
module_name,
Date(activation_date) as activation_date,
Date(deactivation_date) as deactivation_date
Inline
[user_name, module_name, activation_date, deactivation_date,
'USER A', 'MODULE A', '2017/01/01', '2018/03/05',
'USER C', 'MODULE B', '2017/08/01', '2019/02/15',
];
So I have list of users (with info when user was active) and also list of modules that was used by them.
To make it simple let's assume that dates in history table (in this example) are dates when this module "was accessible"
Now when I select some month between activation_deactivation date (i.e. using line chart, like 2028/01/01) I can see that USER_A used MODULE_A. But I want to present also list of other active users that didn't use this module in this time (list of users with possibility of using such a module). And same for module: when I select module then I want to see list of users who use this module and also who didn't but could (based on user creation/removal dates and module activation/deactivation date).
I suppose that I can generate "fake" entries in history table for each user each month each module but this may be huge table so I really want to avoid this.
How can I achieve that ?
Thank you in advance for any help
Regards
