Post reply

Contiguous time slots

  • May 19, 2015 at 3:40 pm

    #1799180

    eirk tsomik (5/19/2015)

    Here is the sample data

    sessionKey locationKey sessionStart sessionEnd instructorKey Flag carKey productTypeKey

    164192 NULL 2015-07-01 06:00:00.000 2015-07-01 08:00:00.000 36754 NULL 133 2

    164196 34 2015-07-01 09:45:00.000 2015-07-01 11:45:00.000 13783 NULL 133 2

    164195 34 2015-07-01 11:45:00.000 2015-07-01 13:45:00.000 13783 NULL 133 2

    164085 13 2015-07-01 14:00:00.000 2015-07-01 16:00:00.000 39097 NULL 133 2

    164096 13 2015-07-01 16:15:00.000 2015-07-01 18:15:00.000 39097 NULL 133 2

    164133 13 2015-07-01 18:15:00.000 2015-07-01 20:15:00.000 39097 NULL 133 2

    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?

  • 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

  • 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

  • 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

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

  • 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)
    Intro to Tally Tables and Functions

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

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