Help finding a date

• Rog Saber

  SSCrazy

  Points: 2548

  More actions

  June 24, 2014 at 7:48 am

  #1724329

  If I run the following, it returns:

  time_sched_start_dttime_sched_end_dt

  2014-06-23 10:45:00.0002014-06-23 12:15:00.000

  I would expect it to return the first 45 minute available timeslot:

  time_sched_start_dttime_sched_end_dt

  2014-06-23 09:15:00.0002014-06-23 10:00:00.000

  ChrisM@Work (6/24/2014)

  ---

  DECLARE @duration INT=45

  SELECT TOP 1

  time_sched_start_dt = MAX(CASE WHEN dir = 'S' THEN dt END),

  time_sched_end_dt = MAX(CASE WHEN dir = 'E' THEN dt END)

  FROM ( -- f

  SELECT dir, dt, Grouper = (ROW_NUMBER() OVER(ORDER BY dt, dir)-1)/2

  FROM ( -- e

  SELECT dir, dt, ct = COUNT(*) OVER(PARTITION BY dt)

  FROM ( -- d

  SELECT dir = 'S', dt = time_sched_start_dt

  FROM #return_schedule sr

  WHERE sr.col_val = ''

  UNION ALL

  SELECT dir = 'E', dt = time_sched_end_dt

  FROM #return_schedule sr

  WHERE sr.col_val = ''

  ) d

  ) e

  WHERE ct = 1

  ) f

  GROUP BY Grouper

  HAVING DATEDIFF(MINUTE,MAX(CASE WHEN dir = 'S' THEN dt END),MAX(CASE WHEN dir = 'E' THEN dt END)) > @duration

  ORDER BY time_sched_start_dt

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  June 24, 2014 at 7:50 am

  #1724332

  Change

  HAVING DATEDIFF(MINUTE,MAX(CASE WHEN dir = 'S' THEN dt END),MAX(CASE WHEN dir = 'E' THEN dt END)) > @duration

  to

  HAVING DATEDIFF(MINUTE,MAX(CASE WHEN dir = 'S' THEN dt END),MAX(CASE WHEN dir = 'E' THEN dt END)) >= @duration

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• Rog Saber

  SSCrazy

  Points: 2548

  More actions

  June 24, 2014 at 7:55 am

  #1724337

  ChrisM@Work (6/24/2014)

  ---

  Change

  HAVING DATEDIFF(MINUTE,MAX(CASE WHEN dir = 'S' THEN dt END),MAX(CASE WHEN dir = 'E' THEN dt END)) > @duration

  to

  HAVING DATEDIFF(MINUTE,MAX(CASE WHEN dir = 'S' THEN dt END),MAX(CASE WHEN dir = 'E' THEN dt END)) >= @duration

  This seems to have worked. I want to thank you for taking the time to look into this and help me out as it was a real challenge for me. I really appreciate it.

• Sachin Nandanwar

  SSCertifiable

  Points: 7699

  More actions

  June 24, 2014 at 8:17 am

  #1724349

  Hello guys,

  As rightly pointed out by Chris the query I posted earlier were having some issues.Please see refined one.

  DECLARE @duration INT=20;

  WITH cte

  AS (SELECT TOP 1 time_sched_start_dt,

  enddt

  FROM (SELECT T.*,

  T1.time_sched_start_dt

  strtdt,

  T1.time_sched_end_dt

  enddt,

  Datediff(minute, T1.time_sched_start_dt,

  T.time_sched_end_dt)

  - @duration diff

  FROM #return_schedule T1

  CROSS apply(SELECT *

  FROM #return_schedule T2

  WHERE T1.col_val = T2.col_val)T

  WHERE Datediff(minute, T.time_sched_start_dt,

  T1.time_sched_end_dt)

  >=

  @duration)T

  ORDER BY diff DESC)

  SELECT Min(time_sched_start_dt)time_sched_start_dt,

  Max(time_sched_end_dt) time_sched_end_dt,

  Sum(CONVERT(INT, col_val))

  FROM (SELECT *

  FROM #return_schedule

  WHERE time_sched_start_dt >= (SELECT time_sched_start_dt

  FROM cte)

  AND time_sched_end_dt <= (SELECT enddt

  FROM cte))t

  HAVING SUM(CONVERT(INT, col_val)) = 0

  Hopefully this one meets all the requirements.

  --------------------------------------------------------------------------------------------------
  I am just an another naive wannabe DBA trying to learn SQL Server

• David Burrows

  SSC Guru

  Points: 65138

  More actions

  June 24, 2014 at 8:27 am

  #1724353

  Removed as wrong solution

  Far away is close at hand in the images of elsewhere.
  Anon.

• Rog Saber

  SSCrazy

  Points: 2548

  More actions

  June 24, 2014 at 8:35 am

  #1724359

  ChrisM@Work (6/24/2014)

  ---

  Change

  HAVING DATEDIFF(MINUTE,MAX(CASE WHEN dir = 'S' THEN dt END),MAX(CASE WHEN dir = 'E' THEN dt END)) > @duration

  to

  HAVING DATEDIFF(MINUTE,MAX(CASE WHEN dir = 'S' THEN dt END),MAX(CASE WHEN dir = 'E' THEN dt END)) >= @duration

  Here's more of my actual data. Results don't appear correct unless I am doing something wrong.

  create table #return_schedule

  (slot_col1 char(15), time_sched_start_dt datetime, time_sched_end_dt datetime)

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('3108337', '2014-06-23 00:00:00.000', '2014-06-23 00:45:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 00:45:00.000', '2014-06-23 01:30:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 01:30:00.000', '2014-06-23 02:15:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 02:15:00.000', '2014-06-23 03:00:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 03:00:00.000', '2014-06-23 03:45:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 03:45:00.000', '2014-06-23 04:30:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 04:30:00.000', '2014-06-23 05:15:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 05:15:00.000', '2014-06-23 06:00:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 06:00:00.000', '2014-06-23 06:45:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 06:45:00.000', '2014-06-23 07:30:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 07:30:00.000', '2014-06-23 08:15:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('CLOSED', '2014-06-23 08:15:00.000', '2014-06-23 09:00:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('CLOSED', '2014-06-23 09:00:00.000', '2014-06-23 09:45:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 09:45:00.000', '2014-06-23 10:30:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 10:30:00.000', '2014-06-23 11:15:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 11:15:00.000', '2014-06-23 12:00:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 12:00:00.000', '2014-06-23 12:45:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 12:45:00.000', '2014-06-23 13:30:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 13:30:00.000', '2014-06-23 14:15:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 14:15:00.000', '2014-06-23 15:00:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 15:00:00.000', '2014-06-23 15:45:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 15:45:00.000', '2014-06-23 16:30:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 16:30:00.000', '2014-06-23 17:15:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 17:15:00.000', '2014-06-23 18:00:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 18:00:00.000', '2014-06-23 18:45:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 18:45:00.000', '2014-06-23 19:30:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 19:30:00.000', '2014-06-23 20:15:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 20:15:00.000', '2014-06-23 21:00:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 21:00:00.000', '2014-06-23 21:45:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 21:45:00.000', '2014-06-23 22:30:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 22:30:00.000', '2014-06-23 23:15:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 23:15:00.000', '2014-06-24 00:00:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-24 00:00:00.000', '2014-06-24 00:45:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-24 00:45:00.000', '2014-06-24 01:30:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-24 01:30:00.000', '2014-06-24 02:15:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-24 02:15:00.000', '2014-06-24 03:00:00.000')

  select * from #return_schedule

  DECLARE @duration INT=20

  SELECT TOP 1

  time_sched_start_dt = MAX(CASE WHEN dir = 'S' THEN dt END),

  time_sched_end_dt = MAX(CASE WHEN dir = 'E' THEN dt END)

  FROM ( -- f

  SELECT dir, dt, Grouper = (ROW_NUMBER() OVER(ORDER BY dt, dir)-1)/2

  FROM ( -- e

  SELECT dir, dt, ct = COUNT(*) OVER(PARTITION BY dt)

  FROM ( -- d

  SELECT dir = 'S', dt = time_sched_start_dt

  FROM #return_schedule sr

  WHERE sr.slot_col1 = ''

  UNION ALL

  SELECT dir = 'E', dt = time_sched_end_dt

  FROM #return_schedule sr

  WHERE sr.slot_col1 = ''

  ) d

  ) e

  WHERE ct = 1

  ) f

  GROUP BY Grouper

  HAVING DATEDIFF(MINUTE,MAX(CASE WHEN dir = 'S' THEN dt END),MAX(CASE WHEN dir = 'E' THEN dt END)) >= @duration

  ORDER BY time_sched_start_dt

  drop table #return_schedule

• Rog Saber

  SSCrazy

  Points: 2548

  More actions

  June 24, 2014 at 8:46 am

  #1724362

  Sachin Nandanwar (6/24/2014)

  ---

  Hello guys,

  As rightly pointed out by Chris the query I posted earlier were having some issues.Please see refined one.

  DECLARE @duration INT=20;

  WITH cte

  AS (SELECT TOP 1 time_sched_start_dt,

  enddt

  FROM (SELECT T.*,

  T1.time_sched_start_dt

  strtdt,

  T1.time_sched_end_dt

  enddt,

  Datediff(minute, T1.time_sched_start_dt,

  T.time_sched_end_dt)

  - @duration diff

  FROM #return_schedule T1

  CROSS apply(SELECT *

  FROM #return_schedule T2

  WHERE T1.col_val = T2.col_val)T

  WHERE Datediff(minute, T.time_sched_start_dt,

  T1.time_sched_end_dt)

  >=

  @duration)T

  ORDER BY diff DESC)

  SELECT Min(time_sched_start_dt)time_sched_start_dt,

  Max(time_sched_end_dt) time_sched_end_dt,

  Sum(CONVERT(INT, col_val))

  FROM (SELECT *

  FROM #return_schedule

  WHERE time_sched_start_dt >= (SELECT time_sched_start_dt

  FROM cte)

  AND time_sched_end_dt <= (SELECT enddt

  FROM cte))t

  HAVING SUM(CONVERT(INT, col_val)) = 0

  Hopefully this one meets all the requirements.

  This returns no results and I was expecting 2014-06-23 02:15:00.000, 2014-06-23 04:30:00.000

  create table #return_schedule

  (slot_col1 char(15), time_sched_start_dt datetime, time_sched_end_dt datetime)

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('3108337', '2014-06-23 00:00:00.000', '2014-06-23 00:45:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 00:45:00.000', '2014-06-23 01:30:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('123', '2014-06-23 01:30:00.000', '2014-06-23 02:15:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 02:15:00.000', '2014-06-23 03:00:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 03:00:00.000', '2014-06-23 03:45:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 03:45:00.000', '2014-06-23 04:30:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 04:30:00.000', '2014-06-23 05:15:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 05:15:00.000', '2014-06-23 06:00:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 06:00:00.000', '2014-06-23 06:45:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 06:45:00.000', '2014-06-23 07:30:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 07:30:00.000', '2014-06-23 08:15:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('CLOSED', '2014-06-23 08:15:00.000', '2014-06-23 09:00:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('CLOSED', '2014-06-23 09:00:00.000', '2014-06-23 09:45:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 09:45:00.000', '2014-06-23 10:30:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 10:30:00.000', '2014-06-23 11:15:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 11:15:00.000', '2014-06-23 12:00:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 12:00:00.000', '2014-06-23 12:45:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 12:45:00.000', '2014-06-23 13:30:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 13:30:00.000', '2014-06-23 14:15:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 14:15:00.000', '2014-06-23 15:00:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 15:00:00.000', '2014-06-23 15:45:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 15:45:00.000', '2014-06-23 16:30:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 16:30:00.000', '2014-06-23 17:15:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 17:15:00.000', '2014-06-23 18:00:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 18:00:00.000', '2014-06-23 18:45:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 18:45:00.000', '2014-06-23 19:30:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 19:30:00.000', '2014-06-23 20:15:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 20:15:00.000', '2014-06-23 21:00:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 21:00:00.000', '2014-06-23 21:45:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 21:45:00.000', '2014-06-23 22:30:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 22:30:00.000', '2014-06-23 23:15:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 23:15:00.000', '2014-06-24 00:00:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-24 00:00:00.000', '2014-06-24 00:45:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-24 00:45:00.000', '2014-06-24 01:30:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-24 01:30:00.000', '2014-06-24 02:15:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-24 02:15:00.000', '2014-06-24 03:00:00.000')

  select * from #return_schedule

  DECLARE @duration INT=120;

  WITH cte

  AS (SELECT TOP 1 time_sched_start_dt,

  enddt

  FROM (SELECT T.*,

  T1.time_sched_start_dt

  strtdt,

  T1.time_sched_end_dt

  enddt,

  Datediff(minute, T1.time_sched_start_dt,

  T.time_sched_end_dt)

  - @duration diff

  FROM #return_schedule T1

  CROSS apply(SELECT *

  FROM #return_schedule T2

  WHERE T1.slot_col1 = T2.slot_col1)T

  WHERE Datediff(minute, T.time_sched_start_dt,

  T1.time_sched_end_dt)

  >=

  @duration)T

  ORDER BY diff DESC)

  SELECT Min(time_sched_start_dt)time_sched_start_dt,

  Max(time_sched_end_dt) time_sched_end_dt,

  Sum(CONVERT(INT, slot_col1))

  FROM (SELECT *

  FROM #return_schedule

  WHERE time_sched_start_dt >= (SELECT time_sched_start_dt

  FROM cte)

  AND time_sched_end_dt <= (SELECT enddt

  FROM cte))t

  HAVING SUM(CONVERT(INT, slot_col1)) = 0

  drop table #return_schedule

• Rog Saber

  SSCrazy

  Points: 2548

  More actions

  June 24, 2014 at 8:54 am

  #1724365

  David Burrows (6/24/2014)

  ---

  My solution

  ;WITH cte (RowID, col_val, time_sched_start_dt, time_sched_end_dt)

  AS (

  SELECT ROW_NUMBER() OVER (ORDER BY a.time_sched_start_dt),

  col_val, time_sched_start_dt, time_sched_end_dt

  FROM #return_schedule a

  ),

  cte2 (GroupID, time_sched_start_dt, time_sched_end_dt)

  AS (

  SELECT RowID-ROW_NUMBER() OVER (ORDER BY time_sched_start_dt),

  time_sched_start_dt, time_sched_end_dt

  FROM cte

  WHERE col_val = ''

  )

  SELECT TOP 1 GroupID,MIN(time_sched_start_dt), MAX(time_sched_end_dt)

  FROM cte2

  GROUP BY GroupID

  HAVING DATEDIFF(minute,MIN(time_sched_start_dt), MAX(time_sched_end_dt)) >= @Duration

  ORDER BY GroupID ASC

  Returns this:

  GroupID(No column name)(No column name)

  22014-06-23 02:15:00.0002014-06-23 08:15:00.000

  Would expect

  GroupID(No column name)(No column name)

  22014-06-23 02:15:00.0002014-06-23 04:30:00.000

  create table #return_schedule

  (slot_col1 char(15), time_sched_start_dt datetime, time_sched_end_dt datetime)

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('3108337', '2014-06-23 00:00:00.000', '2014-06-23 00:45:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 00:45:00.000', '2014-06-23 01:30:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('123', '2014-06-23 01:30:00.000', '2014-06-23 02:15:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 02:15:00.000', '2014-06-23 03:00:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 03:00:00.000', '2014-06-23 03:45:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 03:45:00.000', '2014-06-23 04:30:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 04:30:00.000', '2014-06-23 05:15:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 05:15:00.000', '2014-06-23 06:00:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 06:00:00.000', '2014-06-23 06:45:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 06:45:00.000', '2014-06-23 07:30:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 07:30:00.000', '2014-06-23 08:15:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('CLOSED', '2014-06-23 08:15:00.000', '2014-06-23 09:00:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('CLOSED', '2014-06-23 09:00:00.000', '2014-06-23 09:45:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 09:45:00.000', '2014-06-23 10:30:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 10:30:00.000', '2014-06-23 11:15:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 11:15:00.000', '2014-06-23 12:00:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 12:00:00.000', '2014-06-23 12:45:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 12:45:00.000', '2014-06-23 13:30:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 13:30:00.000', '2014-06-23 14:15:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 14:15:00.000', '2014-06-23 15:00:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 15:00:00.000', '2014-06-23 15:45:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 15:45:00.000', '2014-06-23 16:30:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 16:30:00.000', '2014-06-23 17:15:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 17:15:00.000', '2014-06-23 18:00:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 18:00:00.000', '2014-06-23 18:45:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 18:45:00.000', '2014-06-23 19:30:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 19:30:00.000', '2014-06-23 20:15:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 20:15:00.000', '2014-06-23 21:00:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 21:00:00.000', '2014-06-23 21:45:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 21:45:00.000', '2014-06-23 22:30:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 22:30:00.000', '2014-06-23 23:15:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-23 23:15:00.000', '2014-06-24 00:00:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-24 00:00:00.000', '2014-06-24 00:45:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-24 00:45:00.000', '2014-06-24 01:30:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-24 01:30:00.000', '2014-06-24 02:15:00.000')

  Insert Into #return_schedule

  (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('', '2014-06-24 02:15:00.000', '2014-06-24 03:00:00.000')

  select * from #return_schedule

  DECLARE @duration INT=120;

  ;WITH cte (RowID, slot_col1, time_sched_start_dt, time_sched_end_dt)

  AS (

  SELECT ROW_NUMBER() OVER (ORDER BY a.time_sched_start_dt),

  slot_col1, time_sched_start_dt, time_sched_end_dt

  FROM #return_schedule a

  ),

  cte2 (GroupID, time_sched_start_dt, time_sched_end_dt)

  AS (

  SELECT RowID-ROW_NUMBER() OVER (ORDER BY time_sched_start_dt),

  time_sched_start_dt, time_sched_end_dt

  FROM cte

  WHERE slot_col1 = ''

  )

  SELECT TOP 1 GroupID,MIN(time_sched_start_dt), MAX(time_sched_end_dt)

  FROM cte2

  GROUP BY GroupID

  HAVING DATEDIFF(minute,MIN(time_sched_start_dt), MAX(time_sched_end_dt)) >= @duration

  ORDER BY GroupID ASC

  drop table #return_schedule

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  June 24, 2014 at 8:54 am

  #1724366

  Rog Saber (6/23/2014)

  ---

  I'm having trouble writing a query and could use some help please.

  I want to find the earliest time_sched_start_dt, time_sched_end_dt where col_val is null

  and the datediff between time_sched_start_dt and time_sched_end_dt is at least 60 minutes.

  Looks good to me:

  create table #return_schedule

  (slot_col1 char(15), time_sched_start_dt datetime, time_sched_end_dt datetime)

  Insert Into #return_schedule (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('3108337', '2014-06-23 00:00:00.000', '2014-06-23 00:45:00.000'),

  ('', '2014-06-23 00:45:00.000', '2014-06-23 01:30:00.000'),

  ('', '2014-06-23 01:30:00.000', '2014-06-23 02:15:00.000'),

  ('', '2014-06-23 02:15:00.000', '2014-06-23 03:00:00.000'),

  ('', '2014-06-23 03:00:00.000', '2014-06-23 03:45:00.000'),

  ('', '2014-06-23 03:45:00.000', '2014-06-23 04:30:00.000'),

  ('', '2014-06-23 04:30:00.000', '2014-06-23 05:15:00.000'),

  ('', '2014-06-23 05:15:00.000', '2014-06-23 06:00:00.000'),

  ('', '2014-06-23 06:00:00.000', '2014-06-23 06:45:00.000'),

  ('', '2014-06-23 06:45:00.000', '2014-06-23 07:30:00.000'),

  ('', '2014-06-23 07:30:00.000', '2014-06-23 08:15:00.000'),

  ('CLOSED', '2014-06-23 08:15:00.000', '2014-06-23 09:00:00.000'),

  ('CLOSED', '2014-06-23 09:00:00.000', '2014-06-23 09:45:00.000'),

  ('', '2014-06-23 09:45:00.000', '2014-06-23 10:30:00.000'),

  ('', '2014-06-23 10:30:00.000', '2014-06-23 11:15:00.000'),

  ('', '2014-06-23 11:15:00.000', '2014-06-23 12:00:00.000'),

  ('', '2014-06-23 12:00:00.000', '2014-06-23 12:45:00.000'),

  ('', '2014-06-23 12:45:00.000', '2014-06-23 13:30:00.000'),

  ('', '2014-06-23 13:30:00.000', '2014-06-23 14:15:00.000'),

  ('', '2014-06-23 14:15:00.000', '2014-06-23 15:00:00.000'),

  ('', '2014-06-23 15:00:00.000', '2014-06-23 15:45:00.000'),

  ('', '2014-06-23 15:45:00.000', '2014-06-23 16:30:00.000'),

  ('', '2014-06-23 16:30:00.000', '2014-06-23 17:15:00.000'),

  ('', '2014-06-23 17:15:00.000', '2014-06-23 18:00:00.000'),

  ('', '2014-06-23 18:00:00.000', '2014-06-23 18:45:00.000'),

  ('', '2014-06-23 18:45:00.000', '2014-06-23 19:30:00.000'),

  ('', '2014-06-23 19:30:00.000', '2014-06-23 20:15:00.000'),

  ('', '2014-06-23 20:15:00.000', '2014-06-23 21:00:00.000'),

  ('', '2014-06-23 21:00:00.000', '2014-06-23 21:45:00.000'),

  ('', '2014-06-23 21:45:00.000', '2014-06-23 22:30:00.000'),

  ('', '2014-06-23 22:30:00.000', '2014-06-23 23:15:00.000'),

  ('', '2014-06-23 23:15:00.000', '2014-06-24 00:00:00.000'),

  ('', '2014-06-24 00:00:00.000', '2014-06-24 00:45:00.000'),

  ('', '2014-06-24 00:45:00.000', '2014-06-24 01:30:00.000'),

  ('', '2014-06-24 01:30:00.000', '2014-06-24 02:15:00.000'),

  ('', '2014-06-24 02:15:00.000', '2014-06-24 03:00:00.000')

  select * from #return_schedule

  DECLARE @duration INT=20

  SELECT TOP 1

  time_sched_start_dt = MAX(CASE WHEN dir = 'S' THEN dt END),

  time_sched_end_dt = MAX(CASE WHEN dir = 'E' THEN dt END)

  FROM ( -- f

  SELECT dir, dt, Grouper = (ROW_NUMBER() OVER(ORDER BY dt, dir)-1)/2

  FROM ( -- e

  SELECT dir, dt, ct = COUNT(*) OVER(PARTITION BY dt)

  FROM ( -- d

  SELECT dir = 'S', dt = time_sched_start_dt

  FROM #return_schedule sr

  WHERE sr.slot_col1 = ''

  UNION ALL

  SELECT dir = 'E', dt = time_sched_end_dt

  FROM #return_schedule sr

  WHERE sr.slot_col1 = ''

  ) d

  ) e

  WHERE ct = 1

  ) f

  GROUP BY Grouper

  HAVING DATEDIFF(MINUTE,MAX(CASE WHEN dir = 'S' THEN dt END),MAX(CASE WHEN dir = 'E' THEN dt END)) >= @duration

  ORDER BY time_sched_start_dt

  -- Result

  -- 2014-06-23 00:45:00.000- 2014-06-23 08:15:00.000

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• Rog Saber

  SSCrazy

  Points: 2548

  More actions

  June 24, 2014 at 9:02 am

  #1724369

  ChrisM@Work (6/24/2014)

  ---

  Rog Saber (6/23/2014)

  ---

  I'm having trouble writing a query and could use some help please.

  I want to find the earliest time_sched_start_dt, time_sched_end_dt where col_val is null

  and the datediff between time_sched_start_dt and time_sched_end_dt is at least 60 minutes.

  Looks good to me:

  create table #return_schedule

  (slot_col1 char(15), time_sched_start_dt datetime, time_sched_end_dt datetime)

  Insert Into #return_schedule (slot_col1 , time_sched_start_dt, time_sched_end_dt)

  Values

  ('3108337', '2014-06-23 00:00:00.000', '2014-06-23 00:45:00.000'),

  ('', '2014-06-23 00:45:00.000', '2014-06-23 01:30:00.000'),

  ('', '2014-06-23 01:30:00.000', '2014-06-23 02:15:00.000'),

  ('', '2014-06-23 02:15:00.000', '2014-06-23 03:00:00.000'),

  ('', '2014-06-23 03:00:00.000', '2014-06-23 03:45:00.000'),

  ('', '2014-06-23 03:45:00.000', '2014-06-23 04:30:00.000'),

  ('', '2014-06-23 04:30:00.000', '2014-06-23 05:15:00.000'),

  ('', '2014-06-23 05:15:00.000', '2014-06-23 06:00:00.000'),

  ('', '2014-06-23 06:00:00.000', '2014-06-23 06:45:00.000'),

  ('', '2014-06-23 06:45:00.000', '2014-06-23 07:30:00.000'),

  ('', '2014-06-23 07:30:00.000', '2014-06-23 08:15:00.000'),

  ('CLOSED', '2014-06-23 08:15:00.000', '2014-06-23 09:00:00.000'),

  ('CLOSED', '2014-06-23 09:00:00.000', '2014-06-23 09:45:00.000'),

  ('', '2014-06-23 09:45:00.000', '2014-06-23 10:30:00.000'),

  ('', '2014-06-23 10:30:00.000', '2014-06-23 11:15:00.000'),

  ('', '2014-06-23 11:15:00.000', '2014-06-23 12:00:00.000'),

  ('', '2014-06-23 12:00:00.000', '2014-06-23 12:45:00.000'),

  ('', '2014-06-23 12:45:00.000', '2014-06-23 13:30:00.000'),

  ('', '2014-06-23 13:30:00.000', '2014-06-23 14:15:00.000'),

  ('', '2014-06-23 14:15:00.000', '2014-06-23 15:00:00.000'),

  ('', '2014-06-23 15:00:00.000', '2014-06-23 15:45:00.000'),

  ('', '2014-06-23 15:45:00.000', '2014-06-23 16:30:00.000'),

  ('', '2014-06-23 16:30:00.000', '2014-06-23 17:15:00.000'),

  ('', '2014-06-23 17:15:00.000', '2014-06-23 18:00:00.000'),

  ('', '2014-06-23 18:00:00.000', '2014-06-23 18:45:00.000'),

  ('', '2014-06-23 18:45:00.000', '2014-06-23 19:30:00.000'),

  ('', '2014-06-23 19:30:00.000', '2014-06-23 20:15:00.000'),

  ('', '2014-06-23 20:15:00.000', '2014-06-23 21:00:00.000'),

  ('', '2014-06-23 21:00:00.000', '2014-06-23 21:45:00.000'),

  ('', '2014-06-23 21:45:00.000', '2014-06-23 22:30:00.000'),

  ('', '2014-06-23 22:30:00.000', '2014-06-23 23:15:00.000'),

  ('', '2014-06-23 23:15:00.000', '2014-06-24 00:00:00.000'),

  ('', '2014-06-24 00:00:00.000', '2014-06-24 00:45:00.000'),

  ('', '2014-06-24 00:45:00.000', '2014-06-24 01:30:00.000'),

  ('', '2014-06-24 01:30:00.000', '2014-06-24 02:15:00.000'),

  ('', '2014-06-24 02:15:00.000', '2014-06-24 03:00:00.000')

  select * from #return_schedule

  DECLARE @duration INT=20

  SELECT TOP 1

  time_sched_start_dt = MAX(CASE WHEN dir = 'S' THEN dt END),

  time_sched_end_dt = MAX(CASE WHEN dir = 'E' THEN dt END)

  FROM ( -- f

  SELECT dir, dt, Grouper = (ROW_NUMBER() OVER(ORDER BY dt, dir)-1)/2

  FROM ( -- e

  SELECT dir, dt, ct = COUNT(*) OVER(PARTITION BY dt)

  FROM ( -- d

  SELECT dir = 'S', dt = time_sched_start_dt

  FROM #return_schedule sr

  WHERE sr.slot_col1 = ''

  UNION ALL

  SELECT dir = 'E', dt = time_sched_end_dt

  FROM #return_schedule sr

  WHERE sr.slot_col1 = ''

  ) d

  ) e

  WHERE ct = 1

  ) f

  GROUP BY Grouper

  HAVING DATEDIFF(MINUTE,MAX(CASE WHEN dir = 'S' THEN dt END),MAX(CASE WHEN dir = 'E' THEN dt END)) >= @duration

  ORDER BY time_sched_start_dt

  -- Result

  -- 2014-06-23 00:45:00.000- 2014-06-23 08:15:00.000

  Here is your result:

  -- Result

  -- 2014-06-23 00:45:00.000- 2014-06-23 08:15:00.000

  I would expect:

  -- Result

  -- 2014-06-23 00:45:00.000- 2014-06-23 01:30:00.000

• David Burrows

  SSC Guru

  Points: 65138

  More actions

  June 24, 2014 at 9:11 am

  #1724371

  ;WITH cte (RowID, slot_col1, time_sched_start_dt, time_sched_end_dt)

  AS (

  SELECT ROW_NUMBER() OVER (ORDER BY a.time_sched_start_dt),

  slot_col1, time_sched_start_dt, time_sched_end_dt

  FROM #return_schedule a

  ),

  cte2 (GroupID, time_sched_start_dt, time_sched_end_dt)

  AS (

  SELECT RowID-ROW_NUMBER() OVER (ORDER BY time_sched_start_dt),

  time_sched_start_dt, time_sched_end_dt

  FROM cte

  WHERE slot_col1 = ''

  )

  SELECT TOP 1 MIN(b.time_sched_start_dt),a.time_sched_end_dt

  FROM cte2 a

  LEFT JOIN cte2 b ON b.GroupID = a.GroupID AND b.time_sched_start_dt < a.time_sched_end_dt

  GROUP BY a.GroupID,a.time_sched_end_dt

  HAVING DATEDIFF(minute,MIN(b.time_sched_start_dt),a.time_sched_end_dt) >= @Duration

  ORDER BY a.GroupID ASC,a.time_sched_end_dt ASC

  Far away is close at hand in the images of elsewhere.
  Anon.

• Rog Saber

  SSCrazy

  Points: 2548

  More actions

  June 24, 2014 at 9:14 am

  #1724372

  David Burrows (6/24/2014)

  ---

  ;WITH cte (RowID, slot_col1, time_sched_start_dt, time_sched_end_dt)

  AS (

  SELECT ROW_NUMBER() OVER (ORDER BY a.time_sched_start_dt),

  slot_col1, time_sched_start_dt, time_sched_end_dt

  FROM #return_schedule a

  ),

  cte2 (GroupID, time_sched_start_dt, time_sched_end_dt)

  AS (

  SELECT RowID-ROW_NUMBER() OVER (ORDER BY time_sched_start_dt),

  time_sched_start_dt, time_sched_end_dt

  FROM cte

  WHERE slot_col1 = ''

  )

  SELECT TOP 1 MIN(b.time_sched_start_dt),a.time_sched_end_dt

  FROM cte2 a

  LEFT JOIN cte2 b ON b.GroupID = a.GroupID AND b.time_sched_start_dt < a.time_sched_end_dt

  GROUP BY a.GroupID,a.time_sched_end_dt

  HAVING DATEDIFF(minute,MIN(b.time_sched_start_dt),a.time_sched_end_dt) >= @Duration

  ORDER BY a.GroupID ASC,a.time_sched_end_dt ASC

  I have run this with different criteria and it appears to work great - thank you very much.