I’m looking for help on take month from startdate and enddate and check if month available in previous row then count mid for status=’Red’ in between for 1 year.

create table dbo.m (yrmonth datetime, eid int, mid int, status varchar(10), startdate datetime, enddate datetime)

insert into dbo.m (yrmonth,eid,mid,status,startdate,enddate)
select '2023-10-01',1,2,'Green','2022-10-01','2023-09-30' union
select '2023-12-01',1,2,'Green','2022-12-01','2023-11-30' union
select '2024-01-01',1,2,'Green','2023-01-01','2023-12-31' union
select '2024-06-01',1,2,'Red','2023-06-01','2024-05-31' union
select '2024-07-01',1,2,'Red','2023-07-01','2024-06-30' 

this is what i tried

SELECT 
    m1.mid,
    m1.status,
    COUNT(*) AS Count
FROM 
    dbo.m m1
JOIN 
    dbo.m m2
    ON m1.mid = m2.mid 
    AND m1.status = 'Red'
    AND m1.enddate <> m2.enddate -- Ensure we don't match the same row
    AND MONTH(m1.enddate) = MONTH(m2.startdate)
GROUP BY 
    m1.mid,
    m1.status
ORDER BY 
    m1.mid, 
    m1.status;

Expected output –6th month available in enddate and startdate for that year.

mid| status| count
2| Red | 1
2| Red | 2