What is the best way to query number of customers subscribed during a particular time period

[wierdtonic]

Our dashboard users have a specific request; they want to see data within a particular time period

Below is how my dataset looks like (dates are mm/dd/yy).

User request - To see how many customers were subscribed within a time period regardless of their current status. i.e. even if their current status is cancelled as long as they were active during the user-provided time period they should be counted

Example - User selects time range to be 01/01/2020 - 03/31/2020. Running a query on below data set should return count of 3. [CUST1 as they cancelled after 03/31/2020, CUST3 as they signed up before 01/01/2020 but are still active, CUST5 as they were active for some point during that period]

Problem - While I can write a SQL query with abundant where clauses to achieve this, we want a self-service automated way i.e. we want users to just provide us the time range and get the number. How do we achieve this in Sisense? I am also open to other tools, changing the data model design and other options. 

Customer ID	Subscription Start Date	Subscription End Date	Subscription Status
CUST1	10/11/2019	4/12/2020	Cancelled
CUST2	1/12/2020		Active
CUST3	5/1/2019		Active
CUST4	6/7/2012	7/8/2012	Cancelled
CUST5	1/12/2020	3/14/2020	Cancelled
CUST6	4/12/2020		Active

[HamzaJ]

Hey @wierdtonic 

I think I understand your issue. Due to the fact that these are start and enddates selecting a period works a bit differently for you (/your users) to get the needed result.

For example, if you want this period 01/01/2020 - 03/31/2020

You should set Start Date to <= 03/31/2020 and end_date to > 01/01/2020

If you convert your dates to int (03/31/2020 becomes 20200331) this will be a bit easier to do. 

Another solution would be to join your table with a date-table. This is how we did it. Instructions found here

By joining this date-table with your table you will create a date for each day between start and end (if end is empty then now). This way your users can filter on this Day-field you created. This will select the right records from the database as long as at least one day is in that filtered period. 

Hamza