Count the number of times a date appears between 2 dates. SQL

I have 2 Dates for various items that are stored in a database as such (dd-mm-yyyy):

ItemID|Date Arrived|Date Left
1     |01-02-1985  |01-06-2000
2     |01-02-1985  |01-04-2000

These item should have been tested every year on the same date, lets say the 4th May.

My expected output should be:

Item|NumInspections
1   |16
2   |15

The reason item 2 has less inspections is that it left on the 1st April before the May’s inspection date.

This there a way in SQL to produce an integer showing the number of times the the 4th of May appears between those 2 dates?

If I use DateDiff I get one less occurrence for item 1 because there is 15yr, 4 mths which means I miss once occurrence.

If I get it to always round up then for item 2 I would get one extra as it would be 15yr, 2mths which would round to 16. That incorrect because the second date is before the checking date.

Any help?

Answer

I believe this logic does what you want:

select (year(d2) - year(d1) +
        (case when d1 > datefromparts(year(d1), 5, 4) and d2 < datefromparts(year(d2), 5, 4) then -1
              when d1 <= datefromparts(year(d1), 5, 4) and d2 >= datefromparts(year(d2), 5, 4) then 1
              else 0
         end)
        ) as num_days
from (values (convert(date, '01-02-1985'),  convert(date, '01-06-2000'))) v(d1, d2);

The idea is that if exactly one year passes, then the date appears once. The case then handles the “extremes” — when either both years have May 4th or neither year does.