Find Gaps in a single date column SQL Server

Good Day everyone,

I need your help.

I am trying to detect gaps in a single column of the type Date or DateTime in SQL Server.

Say we have a list of schools and each school has many records and there is a field of uploadDate.

So something like that:

My outcome would be something like that:

Thank you all.

Answer

You can use lead():

select name, dateadd(day, 1, upload_date), dateadd(day, -1, next_upload_date)
from (select t.*,
             lead(upload_date) over (partition by name order by upload_date) as next_upload_date
      from t
     ) t
where next_upload_date <> dateadd(day, 1, upload_date);