Counting days cummulatively over the last year over multiple date intervals within groups

This is a continuation of a previous question. After having resolved my initial question (Previous question), I realized that I needed to add some complexity to my question to properly solve my usecase.

I need to calculate the first 100 days of a year within groups which contains multiple date intervals. The start of the year is set arbitrarily from the first date of the first interval within each group. I also need to be able to output two variables [Location] and [Amount] associated with each interval. My problem is then that some of these intervals will overlap with a single day (identical EndDate and following StartDate), which messes up my count.

I have the following sample data Fiddle for sample data and code attempt

It would like the following output:

I tried using the following (rewritten from previous question):

DECLARE @MinDate DATE = (SELECT MIN(StartDate) FROM SampleData)
DECLARE @MaxDate DATE = (SELECT MAX(EndDate) FROM SampleData)
;WITH n AS 
(
SELECT 0 AS n
UNION ALL
SELECT n + 1 
FROM n 
WHERE n + 1 <= DATEDIFF(DAY, @MinDate, @MaxDate)
)
SELECT DATEADD(DAY, n, @MinDate) AS Date
INTO #Calendar
FROM n 
OPTION (MAXRECURSION 0)
CREATE TABLE #Result
(
Date DATE,
[Group] VARCHAR(20),
[Location] VARCHAR(100),
[Amount] DECIMAL(10,2),
Counted INT,
CumulativeCount INT,
ResetDate DATE
);
DECLARE @Group VARCHAR(20), @Date DATE, @Location VARCHAR(100), @Amount DECIMAL(10,2);
DECLARE GroupCursor CURSOR FOR 
SELECT DISTINCT [Group], [Location], [Amount] FROM SampleData
OPEN GroupCursor
FETCH NEXT FROM GroupCursor INTO @Group, @Location, @Amount
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE DateCursor CURSOR FOR 
SELECT Date FROM #Calendar ORDER BY Date
OPEN DateCursor
FETCH NEXT FROM DateCursor INTO @Date
DECLARE @CumulativeCount INT = 0, @ResetDate DATE = NULL
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @Counted INT = 
(
SELECT CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END
FROM SampleData
WHERE [Group] = @Group AND @Date BETWEEN StartDate AND EndDate
)
IF @Counted = 1 AND (@CumulativeCount = 0 OR DATEDIFF(DAY, @ResetDate, @Date) >= DATEDIFF(DAY, @ResetDate, DATEADD(YEAR, 1, @ResetDate))) --- Altered check to account for leap years. 
BEGIN
SET @CumulativeCount = 1
SET @ResetDate = @Date  
END
ELSE IF @Counted = 1 AND @CumulativeCount < 100 
SET @CumulativeCount = @CumulativeCount + 1
ELSE IF @Counted = 1 AND @CumulativeCount >= 100
SET @Counted = 0
IF  
( 
SELECT CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END
FROM SampleData
WHERE [Group] = @Group AND [Location] = @Location AND [Amount] = @Amount AND @Date BETWEEN StartDate AND EndDate
) = 1
BEGIN
INSERT INTO #Result (Date, [Group], [Location], [Amount], Counted, CumulativeCount, ResetDate)
VALUES (@Date, @Group, @Location, @Amount, @Counted, @CumulativeCount, @ResetDate)
END
FETCH NEXT FROM DateCursor INTO @Date
END
CLOSE DateCursor
DEALLOCATE DateCursor
FETCH NEXT FROM GroupCursor INTO @Group, @Location, @Amount
END
CLOSE GroupCursor
DEALLOCATE GroupCursor
SELECT * FROM #Result
ORDER BY [Group], Date
SELECT 
[Group],
[Location],
[Amount],
FORMAT(Date, 'yyyyMM') AS YearMonth,
SUM(Counted) AS CountDays
FROM #Result
GROUP BY [Group], [Location], [Amount], FORMAT(Date, 'yyyyMM')
ORDER BY [Group], YearMonth, [Location]

This does nearly exactly what I need, but I have a problem with the duplicate dates (example of 2021-02-01). Here, the variable @CumulativeCount does not update properly across the duplicate dates - which leads to counting more than 100 days in the end. I have tried multiple workarounds and fixes but have so far come up with no solution that fixes it (and doesnt break anything).

Here's a snippet of the #Results to show the problem:

Any help would be much appreciated.

Sounds like you're going to want to employ a calendar table, and then use it something like this:

SELECT t.GroupID, t.Location, t.StartDate, t.EndDate, c.Year, c.Month, COUNT(CASE WHEN DATEDIFF(DAY,(DATEADD(YEAR,DATEDIFF(YEAR,t.StartDate,c.Date),t.StartDate)),c.Date) < 100 THEN 1 END) AS CountDays
  FROM @Table t
    INNER JOIN dbo.Calendar(3,1) c
	  ON t.StartDate <= c.Date
	  AND t.EndDate >= c.Date
 GROUP BY t.GroupID, t.Location, t.StartDate, t.EndDate, c.Year, c.Month
 ORDER BY GroupID, Location, c.Year, c.Month

If you can't/don't want to implement a calendar table or function, you could build a pseudo version using a CTE:

;WITH CalendarCTE AS (
SELECT MIN(StartDate) AS Date, MAX(EndDate) AS mxDate, DATEPART(MONTH,MIN(StartDate)) AS Month, DATEPART(YEAR,MIN(StartDate)) AS Year
FROM @Table
UNION ALL
SELECT DATEADD(DAY,1,Date), mxDate, DATEPART(MONTH,DATEADD(DAY,1,Date)) AS Month, DATEPART(YEAR,DATEADD(DAY,1,Date)) AS Year
  FROM CalendarCTE
 WHERE Date < mxDate
)

SELECT t.GroupID, t.Location, t.StartDate, t.EndDate, c.Year, c.Month, COUNT(CASE WHEN DATEDIFF(DAY,(DATEADD(YEAR,DATEDIFF(YEAR,t.StartDate,c.Date),t.StartDate)),c.Date) < 100 THEN 1 END) AS CountDays
  FROM @Table t
    INNER JOIN CalendarCTE c
	  ON t.StartDate <= c.Date
	  AND t.EndDate >= c.Date
 GROUP BY t.GroupID, t.Location, t.StartDate, t.EndDate, c.Year, c.Month
 ORDER BY GroupID, Location, c.Year, c.Month
   OPTION (MAXRECURSION 0)

There's no need for cursors if you take advantage of analytic functions to compute the cumulative days numbers.

My query uses a SQL Server 2022 function to explode the dates into a series of months via cross apply and generate_series(). You can replace that with whatever method you prefer. It also uses date_bucket() to compute a relative reference date for each logical year. Substitute an equivalent calculation if necessary. The rest is just a straightforward summation followed by some case logic to cap at 100 days.

with explode as (
select *,
date_bucket(year, 1, Mnth,
min(StartDate) over (partition by [Group])) as ReferenceDate,
case when Mnth <> eomonth(EndDate) then day(Mnth) else 1 end as Counter
from SampleData data cross apply (
select eomonth(dateadd(month, value, StartDate)) as Mnth
from generate_series(0, 99) -- adjust if 100 months isn't enough...
where dateadd(month, value, StartDate) <= EndDate
) months
), accumulate as (
select *,
sum(Counter) over (partition by [Group], ReferenceDate order by Mnth) as CumDays
from explode
)
select [Group], Location, Amount,
convert(char(6), Mnth, 112) as YearMonth,
case when CumDays <= 100 then Counter
when CumDays - Counter < 100 then 100 - (CumDays - Counter)
else 0 end as CountDays
from accumulate
order by [Group], YearMonth, Location;

https://dbfiddle.uk/ZSYJpC5A