How to return records based on time availability in sql server-CodePudding

I need a SQL query that will return available doctors based on the time availability.

StartTime, EndTime TIME(0) -- data type

INSERT INTO Schedule(PatientId, DoctorId, DayOfWeek,StartTime,EndTime) 
(1,100,'Sunday','8:00 AM','11:00 AM')
INSERT INTO Schedule(PatientId, DoctorId, DayOfWeek,StartTime,EndTime) 
(1,101,'Monday','12:00 PM','03:00 PM')

INSERT INTO Schedule(PatientId, DoctorId, DayOfWeek,StartTime,EndTime) 
(2,100,'Sunday','5:00 PM','7:00 PM')
INSERT INTO Schedule(PatientId, DoctorId, DayOfWeek,StartTime,EndTime) 
(2,101,'Friday','4:00 PM','6:00 PM')

In the given table we can store schedules between patient and doctor. I need a SQL query where the query will return DoctorId. And also per week, a doctor can have a total of 40 hours (8 hours/5 days). so if I pass a parameter that I need the doctors who have >= 50% time available out of 40 hours.

Example: If I want to get DoctorId (100), he might be busy with any patient on Sunday(2 hrs) and Monday (2 hrs). but his 36 hrs still available for the week. the SQL will return the DoctorId (100)

CodePudding user response：

If you take the DATEDIFF(second, StartTime, EndTime) you will get how many seconds that schedule entry covers.

So, if you sum that value up across all of a doctor's rows, you can see how many seconds they're busy for in total.

Then, if you want to see doctors who have more than 50% of their time free, you just look for doctors who have less than 20hrs of scheduled appointments (where 20 hrs == 20 * 60 * 60 seconds)...

SELECT
  DoctorId,
  SUM(DATEDIFF(second, StartTime, EndTime)) / (60.0 * 60.0)   AS hrs_busy
FROM
  Schedule
GROUP BY
  DoctorId
HAVING
  SUM(DATEDIFF(second, StartTime, EndTime)) <= (20 * 60 * 60)

Demo : https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=5bd2fc110d7039acadcb280323df9d77