Turning Records by Date Range into Records by Day/Month Using SQL Server or Vertica

Question

I can use either SQL Server or Vertica as the DB and Tableau as the reporting tool. A solution in any of these mediums would be helpful.

DATA RESOURCES: I have a table (userActivity) with 100 records and a structure of User, StartDate, EndDate

NEED: I am interested in preparing reports by day and month that show "total active days", meaning if User1 has a range of '20180101' to '20180331', they will contribute one day for each day in Jan, Feb, and Mar OR 31, 28 and 31 days if aggregated by month.

GOAL: I will ultimately be aggregating the total active days of all users as the output to achieve a single total for each day/month.

This report will span to perpetuity, so I would prefer solutions that don't hard code CASE/IF-THEN statements by day/month.

Thanks!

Answer 1

• While recursive CTEs are a good candidate for this scenario, it can be handled with tableau alone. Assuming you have this data, here are the steps required to produce the view.

• Create a reference sheet that has all the days expected. Even if you need to cover 25 years from 01/01/2018 to 01/01/2043, that is still less than 10k rows.

• You need two columns with exact same date as Tableau does not allow multiple join conditions on the same column.

• Create an inner join between reference calendar and data using the following criteria.

Build the view