Solved

Find gaps in datetime columns (where starttime of next <> endtime of current)

Posted on 2010-11-17
6
416 Views
Last Modified: 2012-05-10
I need to identify holes in a schedule, basically I am looking for missing data.   Using the following data as a test case I need to identify any timeslots between the variables @daystart and @dayend where there isn't data.

LocID	StartTime		EndTime
 1	11/15/2010 1:00pm	11/15/2010 2:00pm
 1	11/15/2010 2:00pm	11/15/2010 3:00pm
 1	11/15/2010 4:00pm	11/15/2010 5:00pm
 1	11/15/2010 7:00pm	11/15/2010 8:00pm

Declare @daystart datetime
Declare @dayend datetime

Set @daystart = '12:00pm'  --First appointment of the day
Set @dayend = '9:00pm'  --Last appointment of the day

Open in new window


The results would look like this.
 1	11/15/2010 12:00pm	11/15/2010 1:00pm
 1	11/15/2010 3:00pm	11/15/2010 4:00pm
 1	11/15/2010 5:00pm	11/15/2010 7:00pm
 1	11/15/2010 8:00pm	11/15/2010 9:00pm

Open in new window


Any advise or help is greatly appreciated.

Thank you...
0
Comment
Question by:clintnash
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 34158114
I probably know what you're going to say but I'll ask anyways.  Are the "time slots" always in hour increments?  If so, you could always join to a Time_Dimension type table.
0
 
LVL 1

Author Comment

by:clintnash
ID: 34158727
Unfortunately no. The time increments range from 15 minutes to 2 hours.
0
 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 34159436
Something like this maybe?  You can past the whole thing into SSMS and run it.  Note:  The table var I created is just for test and would be replaced by actual table data.

The logic behind it would be to use the RowNumber function to create a unique ID per location ID and use that RowNumber as part of a join to get the previous records value.

declare @myTable table (LocID int,      StartTime datetime,       EndTime datetime)

declare @startDateTime datetime = '11/15/2010 12:00 PM'
declare @endDateTime datetime = '11/15/2010 9:00 PM'

insert into @myTable values ( 1, '11/15/2010 1:00pm', '11/15/2010 2:00pm')
insert into @myTable values ( 1, '11/15/2010 2:00pm', '11/15/2010 3:00pm')
insert into @myTable values ( 1, '11/15/2010 4:00pm', '11/15/2010 5:00pm')
insert into @myTable values ( 1, '11/15/2010 7:00pm', '11/15/2010 8:00pm');

with MyTableWithStartAndEnd as
(
      select 1 as LocId, @startDateTime as StartTime, @startDateTime as EndTime
      union
      select * from @myTable
      union
      select 1, @endDateTime as StartTime, @endDateTime as EndTime

)

, MyTableWithCounter as
(
      select ROW_NUMBER() over (partition by LocId order by endtime) as Instance_Counter
            , *
                  
      from      MyTableWithStartAndEnd
)

, MyTableWithRanges as
(
      select
             a.[LocId]
            
            ,a.[StartTime]
            
            ,a.EndTime
      
            ,a.Instance_Counter
      
            ,abs((select datediff(minute, a.EndTime, b.StartTime) from MyTableWithCounter b where a.locId = b.LocID and b.[Instance_Counter] = a.[Instance_Counter] + 1)) as GapRangeInMinutes

      from      MyTableWithCounter a
)

 select      
 
            StartTime
      ,      DATEADD(MINUTE, GapRangeInMinutes, StartTime) as EndTime
      ,      case
                  when GapRangeInMinutes < 60 then 0
                  else GapRangeInMinutes/60                  
            end      as      GapInHours
      ,      GapRangeInMinutes                        as      GapInMinutes
 
 from      MyTableWithRanges
 where ISNULL(GapRangeInMinutes,0) > 0
 
 
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 1

Author Comment

by:clintnash
ID: 34159877
I think this is almost there.  The number of results are correct but the times in the results are not.  I cut and pasted the code and am getting the following for output.

Start Time                        End Time                       GinH  GinMin
Nov 15 2010 12:00PM	Nov 15 2010  1:00PM	1	60
Nov 15 2010  2:00PM	Nov 15 2010  3:00PM	1	60
Nov 15 2010  4:00PM	Nov 15 2010  6:00PM	2	120
Nov 15 2010  7:00PM	Nov 15 2010  8:00PM	1	60

Open in new window


The results should be

Start Time                        End Time                       GinH  GinMin
Nov 15 2010 12:00PM	Nov 15 2010  1:00PM	1	60
Nov 15 2010 3:00PM	Nov 15 2010  4:00PM	1	60
Nov 15 2010 5:00PM	Nov 15 2010  7:00PM	2	120
Nov 15 2010 8:00PM	Nov 15 2010  9:00PM	1	60

Open in new window


It seems like after the first row that the rest of the timeslots are off by an hour?  Thank you very much for your help.

0
 
LVL 14

Accepted Solution

by:
Christopher Gordon earned 500 total points
ID: 34161022
--try this logic

declare @myTable table (LocID int,      StartTime datetime,       EndTime datetime)

declare @startDateTime datetime = '11/15/2010 12:00 PM'
declare @endDateTime datetime = '11/15/2010 9:00 PM'

insert into @myTable values ( 1, '11/15/2010 1:00pm', '11/15/2010 2:00pm')
insert into @myTable values ( 1, '11/15/2010 2:00pm', '11/15/2010 3:00pm')
insert into @myTable values ( 1, '11/15/2010 4:00pm', '11/15/2010 5:00pm')
insert into @myTable values ( 1, '11/15/2010 7:00pm', '11/15/2010 8:00pm');

with MyTableWithStartAndEnd as
(
      select 1 as LocId, @startDateTime as StartTime, @startDateTime as EndTime
      union
      select * from @myTable
      union
      select 1, @endDateTime as StartTime, @endDateTime as EndTime

)

, MyTableWithCounter as
(
      select ROW_NUMBER() over (partition by LocId order by endtime) as Instance_Counter
            , *
                 
      from      MyTableWithStartAndEnd
)

, MyTableWithRanges as
(
      select      
            a.LocId
      ,      a.Instance_Counter
      ,      a.StartTime
      ,      A.EndTime
      ,      b.StartTime            as      NextStartTime
      ,      DATEDIFF(minute, a.EndTime, b.StartTime) as TimeSpanMinutes

      from      MyTableWithCounter a
      
      left outer join MyTableWithCounter b on
            (a.Instance_Counter + 1) = b.Instance_Counter
)

select      
            LocId
      ,      Instance_Counter
      ,      convert(nvarchar(30), EndTime, 109)                  as      StarTimeOfHiatus
      ,      TimeSpanMinutes                                                as      SpanofHiatusMinuts
      
      ,   case
                    when TimeSpanMinutes < 60 then 0
                    else TimeSpanMinutes / 60                  
            end                                                                  as      SpanofHiatusHours
      
      ,      convert(nvarchar(30), dateadd(minute, timespanminutes, EndTime), 109)      as      EndTimeHiatus

from      MyTableWithRanges

where ISNULL(TimeSpanMinutes,0) > 0
0
 
LVL 1

Author Closing Comment

by:clintnash
ID: 34166977
Brilliant!  Thank you so much for your time.  This was another opportunity to not just get an answer to a problem but learn something in the process.  Thanks again...
0

Featured Post

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Sql Permission 6 74
SQL 2005 - Memory Table Column Names 11 81
How to import SQL 2000 database to SQL 2014 5 160
My Query is not giving correct result. Please help 5 55
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question