Search code examples
sqldate-range

Identify double seat bookings via sql

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- enter image description here

as you can see it is not picking up any date ranges.. only for a single date..But there were double bookings in case date ranges which this query is not showing. Can anyone please help me with this?

Solution

  • As others have suggested, you should remove the email part of your question and post that separately once this is resolved.

    For simplicity, I've used temp tables to demonstrate this but it should be easy to convert to a CTE is you wish.

    The key to the is having a Date table. If you don't have one, there are plenty of examples of how to generate one quickly. In this case my date table is called [Config].[DatesTable]

    CREATE TABLE #t (desk_id int, date_from date, date_to date, EmpName varchar(10));
insert into #t VALUES
(1, '2022-12-25', '2023-01-01', 'Dave'),
(2, '2023-01-15', '2023-01-15', 'Jane'),
(2, '2023-01-12', '2023-01-20', 'Bob'),
(2, '2023-01-15', '2023-01-17', 'Mary');

-- desks and the dates they are over booked on
SELECT desk_id, TheDate 
INTO #OverBookedDeskByDate
    FROM (SELECT t.* , dt.TheDate
            FROM #t t 
            JOIN Config.DatesTable dt on dt.TheDate between t.date_from and t.date_to
        ) a 
    GROUP BY desk_id, TheDate
    HAVING Count(*) >1

-- find the bookings that overlap these desks/dates
SELECT t.*, o.TheDate FROM #OverBookedDeskByDate o 
    JOIN #t t on o.TheDate between t.date_from and t.date_to
ORDER by EmpName, desk_id, TheDate

    I've created 3 bookings with some overlapping dates for desk 2.

    Here are the results

    enter image description here