I go you one better:
select m.id,
m.licenceId,
m.sd,
m.ed,
count(c.id) as NumberOfOverlaps
from mytable m
left outer join mytable c on m.licenceId = c.licenceId AND c.ed > m.sd AND c.sd < m.ed
group by m.id,
m.licenceId,
m.sd,
m.ed
order by m.licenceId, m.sd
see if that works
UPDATE:
I added a stuff to show ids that were being included in the overlaps:
select m.id,
m.licenceId,
m.sd,
m.ed,
count(c.id) as flag,
STUFF((SELECT DISTINCT ', ' + NULLIF(cast(t2.id as nvarchar(max)),'')
FROM mycte t2
WHERE m.licenceId = t2.licenceId AND t2.ed > m.sd AND t2.sd < m.ed
FOR XML PATH(''),TYPE
).value('.','VARCHAR(MAX)')
,1,2,'') AS checkcsv
from mycte m
left outer join mycte c on m.licenceId = c.licenceId AND c.ed > m.sd AND c.sd < m.ed
group by m.id,
m.licenceId,
m.sd,
m.ed
order by m.licenceId, m.sd
This showed that each row matched itself. 12 did not because I got the dates mixed up in my text data (end was before start)
To eliminate this I have included an extra condition:
Fixed:
select m.id,
m.licenceId,
m.sd,
m.ed,
case count(c.id) when 0 then 0 else 1 end as flag
from mycte m
left outer join mycte c on m.licenceId = c.licenceId AND c.ed > m.sd AND c.sd < m.ed AND c.id != m.id
group by m.id,
m.licenceId,
m.sd,
m.ed
order by m.licenceId, m.sd
Output including checkcsv not in query above:
id lId sd ed flag checkcsv
4 L1 2011-12-02 2012-12-18 0 NULL
3 L1 2012-12-20 2013-01-20 1 1, 2
1 L1 2013-01-01 2013-01-15 1 2, 3
2 L1 2013-01-10 2013-01-20 1 1, 3
10 L1 2013-01-25 2013-11-30 0 NULL
7 L2 2012-12-02 2012-12-30 1 8
8 L2 2012-12-05 2012-12-30 1 7
5 L2 2013-01-01 2013-12-15 1 6
6 L2 2013-01-02 2013-10-12 1 5
12 L2 2014-12-01 2014-11-30 0 NULL
9 L3 2012-11-01 2012-11-15 0 NULL
11 L3 2014-01-01 2014-11-30 0 NULL