I have to make a report to identify double seat bookings . One can book a seat for a date range or a single date. Like the columns date_from to date_to can be a single day or a range( like from 16th Jan till 16th Jan or from 10th Jan to 30th Jan) The problem is that the system allows double booking in case when there is an overlapping date range like if someone wants to book seat no 7 from 10th Jan to 16th Jan and someone books the same seat from 12thJan to 13th Jan. But it should not, that is what I have to flag about

I have tried writing the below query but my query does not identify anything in date ranges.. it only works for single dates. I would need to first break these date ranges in single dates and then run my query to work -

;with duplicate_seat(desk_id,date_from,date_to,name) as
  (

  select  da.desk_id, da.date_from,da.date_to, hr.name as name
       FROM [human_resources].[dbo].[desks_temporary_allocations]  da
       JOIN[human_resources].[dbo].hrms_mirror hr ON hr.sage_id = da.sage_id
       

       )
 select ds.desk_id,ds.date_from,ds.date_to,count(ds.desk_id)as occurences,min(ds.name)as Name1,max(ds.name) as Name2
 from duplicate_seat ds
 where ds.name like ('priyanka%')
group by ds.desk_id,ds.date_from,ds.date_to
having count(ds.desk_id)>1

This will give result like-