Tracking members duration, with overlaping memberships.

SELECT
MIN(`Create Date`) as 'Member Origination Date'
MAX(`End Date`) as 'Member Termination Date'

FROM yourData

GROUP BY(`Member ID`)

Then in your card you filter out anyone who has a Member Termination Date in the past to show only current members.

Using the examples I show, I manually count:

January 1, 2023 → June 1, 2023
Includes both start and end date

Result: 152 days

July 1, 2023 → August 1, 2023
Includes both start and end

Result: 32 days

January 1, 2024 → February 15, 2024
2024 is a leap year

Result: 46 days

For a total of 230 days.

To solve this I created a SQL query to produce a calendar table. This will hold a single date for a range of my choosing. I exported the table from SQL to Domo.

DECLARE @StartDate DATE = '2010-01-01';

DECLARE @EndDate DATE = '2035-12-31';


WITH Calendar AS (

    SELECT @StartDate AS CalendarDate

    UNION ALL

    SELECT DATEADD(DAY, 1, CalendarDate)

    FROM Calendar

    WHERE CalendarDate < @EndDate

)

SELECT 

    CalendarDate,

    DATENAME(WEEKDAY, CalendarDate) AS Day_Name,

    DATEPART(DAY, CalendarDate) AS Day,

    DATEPART(MONTH, CalendarDate) AS Month,

    DATENAME(MONTH, CalendarDate) AS Month_Name,

    DATEPART(YEAR, CalendarDate) AS Year,

    DATEPART(WEEK, CalendarDate) AS Week_Number,

    DATEPART(QUARTER, CalendarDate) AS Quarter

FROM Calendar

OPTION (MAXRECURSION 32767);

Sample membership as a csv import

Member_ID,Start_Date,End_Date

101,2023-01-01,2023-03-31

101,2023-03-15,2023-06-01

101,2023-07-01,2023-08-01

101,2024-01-01,2024-01-31

101,2024-01-15,2024-02-15

Magic ETL using a tile to make sure end dates are not null. So I set these to current date.

On the JOIN condition I used:

And the end result gives me the answer I got when I manually calculated and removed overlaps.