Tracking members duration, with overlaping memberships.

sangelov · 2025-06-03T03:48:31+00:00

There was an error rendering this rich post.

I have the following case.

One member can have multiple memberships at the same time, and can cancel one or many, and after that, resume or enroll in a new one. How can I track the day a member has been with our company? I have the column with the membership name that is not in the photo.

Find more posts tagged with

Accepted answers

All comments

ColemenWilson

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.

ArborRose

Depending upon your criteria, it may not be as simple as aggregating by group. Consider sample data like this:

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

Some memberships may not overlap. And in other scenarios, you can't group and count each membership and sum them together because that means some of the days would be counted twice for the same member. Unless that is what you wanted.

Member_ID	Start_Date	End_Date	Days
101	2024-01-01	2024-01-31	31
101	2024-01-15	2024-02-15	32

So you would have to merge the overlapping periods / adjacent ranges. Then count the total span of those merged periods.

ArborRose

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.