Geek asks and answers

SQL Filter Date Range Query

I have a list of schedule entries

Staffchedules

enter image description here

Some of them have already been booked.

Bookedchecheles

enter image description here

There are also several holidays

Holidays

enter image description here

I want to get only available employee schedules

Expected Schedules= StaffSchedule - BookedSchedule-Holidays

ie, I want a row of 2.6 only from the StaffSchedule table

Below is the query I tried, but it does not show results

with NonBookingSlots as
(
select StartdateTime,EndDateTime from Holidays
union all
select StartdateTime,EndDateTime from BookedSchedules
)

SELECT
    StaffId, StartdateTime, EndDateTime
FROM StaffSchedule
WHERE 
not exists (select 1
                from NonBookingSlots h
                where cast(StartdateTime as DATETIME) between 
                cast(h.startdatetime as DATETIME) 
                and cast(h.enddatetime as DATETIME)
           )

SQL FIDDLE DEMO

+3
source share
2 answers

For all the examples, I made the assumption that the start and end times in BookedScheduleswill coincide exactly with the start and end times StaffSchedules.

With CTE, similar to the question:

, , . .

with NonBookingSlots as
(
  select null as StaffId,StartdateTime,EndDateTime from Holidays
  union all
  select StaffId,StartdateTime,EndDateTime from BookedSchedules
)

select
  StaffId, StartdateTime, EndDateTime
from 
  StaffSchedule
where
  not exists(
    select
      1
    from 
      NonBookingSlots
    where
      StaffSchedule.StaffId = isnull(NonBookingSlots.StaffId,StaffSchedule.StaffId)
      and (
        (
          StaffSchedule.StartDateTime =  NonBookingSlots.StartDateTime
          and StaffSchedule.EndDateTime = NonBookingSlots.EndDateTime
        ) or (
          StaffSchedule.StartDateTime <  NonBookingSlots.EndDateTime
          and StaffSchedule.EndDateTime > NonBookingSlots.StartDateTime
        )
     )
  )

SQL Fiddle: http://sqlfiddle.com/#!3/9cbf4/14

CTE:

, .

select
  StaffId, StartdateTime, EndDateTime
from 
  StaffSchedule
where
  not exists(
    select
      1
    from 
      BookedSchedules
    where
      StaffSchedule.StaffId = BookedSchedules.StaffId
      and StaffSchedule.StartDateTime =  BookedSchedules.StartDateTime
      and StaffSchedule.EndDateTime = BookedSchedules.EndDateTime
  ) and not exists(
    select
      1
    from 
      Holidays
    where
      StaffSchedule.StartDateTime <  Holidays.EndDateTime
      and StaffSchedule.EndDateTime > Holidays.StartDateTime
  )

SQL Fiddle: http://sqlfiddle.com/#!3/9cbf4/15

- :

BookedSchedules StaffSchedule, StaffSchedule BookedSchedules. .

select
  StaffId, StartdateTime, EndDateTime
from 
  StaffSchedule
where
  not exists(
    select
      1
    from 
      BookedSchedules
    where
      StaffSchedule.Id = BookedSchedules.StaffScheduleId
  ) and not exists(
    select
      1
    from 
      Holidays
    where
      StaffSchedule.StartDateTime <=  Holidays.EndDateTime
      and StaffSchedule.EndDateTime >= Holidays.StartDateTime
  )

SQL Fiddle: http://sqlfiddle.com/#!3/8a684/3

+3
select *
  from dbo.StaffSchedule x
 where not exists (select 'y'
          from dbo.BookedSchedules y
         where y.staffid = x.staffid
           and y.startdatetime = x.startdatetime
           and y.enddatetime = x.enddatetime)
   and not exists (select 'z'
          from dbo.Holidays z
         where cast(z.startdatetime as date) =
               cast(x.startdatetime as date))

SQL Fiddle : http://sqlfiddle.com/#!3/07698/19/0

- , :

select *
  from dbo.StaffSchedule x
 where not exists (select 'y'
          from dbo.BookedSchedules y
         where y.staffid = x.staffid
           and y.startdatetime = x.startdatetime
           and y.enddatetime = x.enddatetime)
   and not exists (select 'z'
          from dbo.Holidays z
         where cast(z.startdatetime as date) <=
               cast(x.startdatetime as date)
           and cast(z.enddatetime as date) >=
               cast(x.enddatetime as date))
+1

More articles:


All Articles