Contiguous time slots

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  May 19, 2015 at 3:40 pm

  #1799180

  eirk tsomik (5/19/2015)

  ---

  Here is the sample data

  sessionKeylocationKeysessionStartsessionEndinstructorKeyFlagcarKeyproductTypeKey

  164192NULL2015-07-01 06:00:00.0002015-07-01 08:00:00.00036754NULL1332

  164196342015-07-01 09:45:00.0002015-07-01 11:45:00.00013783NULL1332

  164195342015-07-01 11:45:00.0002015-07-01 13:45:00.00013783NULL1332

  164085132015-07-01 14:00:00.0002015-07-01 16:00:00.00039097NULL1332

  164096132015-07-01 16:15:00.0002015-07-01 18:15:00.00039097NULL1332

  164133132015-07-01 18:15:00.0002015-07-01 20:15:00.00039097NULL1332

  What is the structure of the table? How do I cut/paste/insert this data?

  Have you even bothered to read that article I pointed you to?

• eirk tsomik

  Ten Centuries

  Points: 1032

  More actions

  May 19, 2015 at 3:44 pm

  #1799183

  you can just use this pseudo code

  DECLARE @Results TABLE (

  sessionKey int NOT NULL,

  locationKey smallint,

  sessionStart datetime NOT NULL,

  sessionEnd datetime NOT NULL,

  instructorKey int NOT NULL,

  Flag int,

  carKey smallint NOT NULL,

  productTypeKey smallint NOT NULL )

  INSERT INTO @Results

  SELECT 164192, NULL, '2015-07-01 06:00:00', '2015-07-01 08:00:00', 36754, NULL, 133, 2 UNION ALL

  SELECT 164193, 34, '2015-07-01 08:00:00', '2015-07-01 10:00:00', 36754, NULL, 133, 2 UNION ALL

  --SELECT 164196, 34, '2015-07-01 09:45:00', '2015-07-01 11:45:00', 13783, NULL, 133, 2 UNION ALL

  SELECT 164195, 34, '2015-07-01 11:45:00', '2015-07-01 13:45:00', 13783, NULL, 133, 2 UNION ALL

  SELECT 164085, 13, '2015-07-01 14:00:00', '2015-07-01 16:00:00', 39097, NULL, 133, 2 UNION ALL

  SELECT 164096, 13, '2015-07-01 16:15:00', '2015-07-01 18:15:00', 39097, NULL, 133, 2 UNION ALL

  SELECT 164133, 13, '2015-07-01 18:15:00', '2015-07-01 20:15:00', 39097, NULL, 133, 2

  SELECT a.sessionKey,

  a.locationKey,

  a.instructorKey,

  a.sessionStart,

  a.sessionEnd,

  b.sessionStart AS NextStart,

  b.sessionEnd AS NextEnd,

  b.instructorKey AS NextInstr

  FROM @Results a

  JOIN @Results b

  ON (b.instructorKey = a.instructorKey OR b.carKey = a.carKey)

  AND b.sessionStart BETWEEN DATEADD(n, -29, a.sessionEnd) AND DATEADD(n, 29, a.sessionEnd)

  WHERE a.sessionStart = '2015-07-01 11:45:00'

  ---

  Kindest Regards,

  Web programmer

• eirk tsomik

  Ten Centuries

  Points: 1032

  More actions

  May 19, 2015 at 3:47 pm

  #1799184

  DECLARE @Results TABLE (

  sessionKey int NOT NULL,

  locationKey smallint,

  sessionStart datetime NOT NULL,

  sessionEnd datetime NOT NULL,

  instructorKey int NOT NULL,

  Flag int,

  carKey smallint NOT NULL,

  productTypeKey smallint NOT NULL )

  INSERT INTO @Results

  SELECT 164192, NULL, '2015-07-01 06:00:00', '2015-07-01 08:00:00', 36754, NULL, 133, 2 UNION ALL

  SELECT 164193, 34, '2015-07-01 08:00:00', '2015-07-01 10:00:00', 36754, NULL, 133, 2 UNION ALL

  --SELECT 164196, 34, '2015-07-01 09:45:00', '2015-07-01 11:45:00', 13783, NULL, 133, 2 UNION ALL

  SELECT 164195, 34, '2015-07-01 11:45:00', '2015-07-01 13:45:00', 13783, NULL, 133, 2 UNION ALL

  SELECT 164085, 13, '2015-07-01 14:00:00', '2015-07-01 16:00:00', 39097, NULL, 133, 2 UNION ALL

  SELECT 164096, 13, '2015-07-01 16:15:00', '2015-07-01 18:15:00', 39097, NULL, 133, 2 UNION ALL

  SELECT 164133, 13, '2015-07-01 18:15:00', '2015-07-01 20:15:00', 39097, NULL, 133, 2

  SELECT a.sessionKey,

  a.locationKey,

  a.instructorKey,

  a.sessionStart,

  a.sessionEnd,

  b.sessionStart AS NextStart,

  b.sessionEnd AS NextEnd,

  b.instructorKey AS NextInstr

  FROM @Results a

  JOIN @Results b

  ON (b.instructorKey = a.instructorKey OR b.carKey = a.carKey)

  AND b.sessionStart BETWEEN DATEADD(n, -29, a.sessionEnd) AND DATEADD(n, 29, a.sessionEnd)

  WHERE a.sessionStart = '2015-07-01 11:45:00'

  ---

  Kindest Regards,

  Web programmer

• eirk tsomik

  Ten Centuries

  Points: 1032

  More actions

  May 19, 2015 at 3:48 pm

  #1799185

  you can just use pseudo code

  DECLARE @Results TABLE (

  sessionKey int NOT NULL,

  locationKey smallint,

  sessionStart datetime NOT NULL,

  sessionEnd datetime NOT NULL,

  instructorKey int NOT NULL,

  Flag int,

  carKey smallint NOT NULL,

  productTypeKey smallint NOT NULL )

  INSERT INTO @Results

  SELECT 164192, NULL, '2015-07-01 06:00:00', '2015-07-01 08:00:00', 36754, NULL, 133, 2 UNION ALL

  SELECT 164193, 34, '2015-07-01 08:00:00', '2015-07-01 10:00:00', 36754, NULL, 133, 2 UNION ALL

  --SELECT 164196, 34, '2015-07-01 09:45:00', '2015-07-01 11:45:00', 13783, NULL, 133, 2 UNION ALL

  SELECT 164195, 34, '2015-07-01 11:45:00', '2015-07-01 13:45:00', 13783, NULL, 133, 2 UNION ALL

  SELECT 164085, 13, '2015-07-01 14:00:00', '2015-07-01 16:00:00', 39097, NULL, 133, 2 UNION ALL

  SELECT 164096, 13, '2015-07-01 16:15:00', '2015-07-01 18:15:00', 39097, NULL, 133, 2 UNION ALL

  SELECT 164133, 13, '2015-07-01 18:15:00', '2015-07-01 20:15:00', 39097, NULL, 133, 2

  SELECT a.sessionKey,

  a.locationKey,

  a.instructorKey,

  a.sessionStart,

  a.sessionEnd,

  b.sessionStart AS NextStart,

  b.sessionEnd AS NextEnd,

  b.instructorKey AS NextInstr

  FROM @Results a

  JOIN @Results b

  ON (b.instructorKey = a.instructorKey OR b.carKey = a.carKey)

  AND b.sessionStart BETWEEN DATEADD(n, -29, a.sessionEnd) AND DATEADD(n, 29, a.sessionEnd)

  WHERE a.sessionStart = '2015-07-01 11:45:00'

  ---

  Kindest Regards,

  Web programmer

• pietlinden

  SSC Guru

  Points: 63506

  More actions

  May 19, 2015 at 4:12 pm

  #1799194

  Please read this article[/url] and follow the instructions.

  Well, unless you don't really care about getting help.

• Jeff Moden

  SSC Guru

  Points: 1004392

  More actions

  May 19, 2015 at 4:50 pm

  #1799199

  @eirk tsomik,

  I'm not sure why but it looks like 3 posts where you posted some readily consumable code were deleted by that autospam detector or an over-zealous monitor. Did you want me to undelete one of them for you?

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)