Contiguous time slots

  • 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?

  • 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

  • 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

  • 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

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

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

  • @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)

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply