0

I have a table with time entries having start and end time. I want to get the time entries which are not there in the table.

Example: I have time entry having start time 08.00 - 09.00 and other for 10.20 - 11.00. I need a record which contains 09.00 - 10.19. As I need to do it for multiple occurrences, can anybody help me out to find this complex query?

enter image description here

i have a time range to show non occuring entries beteen 07.00 to 17.00 then it should return me 7.00 to 8.45 and 14.00 to 17.00

Pankaj Kumar
  • 35
  • 6
  • Can you share the schema you are using ? – driis Jul 30 '17 at 09:50
  • Simplest way I see this working is using a CTE to have every minute of that day in a column, left joining that with your entries table in order to find out which minutes are "taken" and which are not, filtering out the "taken" ones and then grouping the remaining ones for intervals :) – Mihai Ovidiu Drăgoi Jul 30 '17 at 09:54
  • https://stackoverflow.com/questions/20980891/how-to-write-recursive-cte-in-sql-server-2012-for-hour-and-min might be a good place to start, only you need one minute intervals – Mihai Ovidiu Drăgoi Jul 30 '17 at 10:05
  • @driis please see image description to get the idea of the schema. – Pankaj Kumar Jul 30 '17 at 10:13
  • Possible duplicate of [SQL Query to show gaps between multiple date ranges](https://stackoverflow.com/questions/9604400/sql-query-to-show-gaps-between-multiple-date-ranges) – TT. Jul 30 '17 at 10:35
  • Base on your example, time starts at 8 and ends at 8:45 in first row and 9:30 to 9:45 in second row which means difference/gap between first row and second row is 8:46 to 9:29 you mean you need a record with that? – Kannan Kandasamy Jul 30 '17 at 10:46

2 Answers2

0

I lack reputation to do a comment (the comments by Mihai and TT were amazing and interesting), but a possible solution may be as simple as

SELECT a.endDT, Min(b.startDT)
FROM sched a, sched b
WHERE a.endDT<b.startDT
GROUP BY a.endDT

that will return for your sample data

2017-07-30 08:45:00.000 2017-07-30 09:30:00.000
2017-07-30 09:45:00.000 2017-07-30 10:30:00.000
2017-07-30 11:45:00.000 2017-07-30 13:15:00.000
2017-07-30 13:45:00.000 2017-07-30 14:00:00.000

However, as the comments of Mihai and TT point out, this will not get the time between say midnight and 8am, the first record.

Dave McRae
  • 1
  • 3
  • It solves the prob but here scenario is bit different I i have a time reange to show non occuring entries beteen 07.00 to 17.00 then it should return me 7.00 to 8.45 and 14.00 to 17.00 – Pankaj Kumar Jul 31 '17 at 05:23
0

I cannot tell what your sample data has to do with your description. But the problem seems to be solved by lag(). For the data you have provided:

select activity, prev_endtime as gap_start, starttime as gap_end
from (select t.*,
             lag(endtime) over (partition by activity order by id) as prev_endtime
      from t
     ) t
where starttime <> prev_endtime;

I should note that this will not work for all possible combinations of start times and end times. But, your time slots don't appear to overlap and they appear to be ordered by id, so this should work.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • It solves the prob but here scenario is bit different I i have a time reange to show non occuring entries beteen 07.00 to 17.00 then it should return me 7.00 to 8.45 and 14.00 to 17.00 – Pankaj Kumar Jul 31 '17 at 05:23