Generate List of dates between 2 dates for each Id

Viewed 12

I have a table with PersonId's that each have a FirstSubscription date and LastSubscriptionDate.

What I need to do is between those 2 dates, generate 1 date for each month. This is for reporting purposes on the front end, as this data will end up inside PowerBI and I need these dates to join to a ReportingCalendar.

This Calendar is accessible by SQL so it can be used in this calculation. I am using it to generate the dates (using first of the month) between the First and LastSubDate but I need to find a way to join this with the rest of the ID's that way I get a list of date for each ID.

Here is my code to generate the dates.

DECLARE @MinDate DATE
DECLARE @MaxDate DATE

SET @MinDate = '2020-08-31'
SET @MaxDate = '2022-08-30'

SELECT  DATEADD(month, DATEDIFF(month, 0, date), 0)
FROM    dbo.ReportingCalendar
WHERE   Date >= @MinDate
AND     Date < @MaxDate

GROUP BY
DATEADD(month, DATEDIFF(month, 0, date), 0)

My PersonSubscription table looks like this

|PersonId|FirstSubDate|LastSubDate|
|--------|------------|-----------|
|1186    |8/31/2020   |8/30/2022  |
|2189    |7/30/2019   |7/31/2021  |

So I would want to end up with an output where each PersonId has 1 entry for each month between those 2 dates. So PersonId has 25 entries from 8/2020 until 8/2022. We don't care about the actual date of the sub since this data is looked at monthly and will primarily be looked at using a Distinct Count each month, so we only care if they were subbed at any time in that month.

1 Answers

I just needed to do a Cross Apply.

I took my code that got me all of the PersonId's and their FirstSubDate and LastSubDate and then did a cross apply to the code I listed above, referencing the MinDate and MaxDate with the FirstSubDate and LastSubDate.

Related