Random Scheduling

Question

I have the following table in my database:

tbl1
PK
ClientID
ScheduleDay
Time1Start
Time1Stop
Time2Start
Time2Stop
Time3Start
Time3Stop
Status

Here is some sample data

ID ClientID ScheduleDay Time1Start Time1Stop Time2Start Time2Stop Time3Start Time3Stop    
-- -------- ----------- ---------- --------- ---------- --------- ---------- ---------  
1     3       Sunday      0000       0800       1000      1300      NULL       NULL  
2     3       Monday      0000       2359       NULL      NULL      NULL       NULL  
3     3       Tuesday     1000       1200       1330      1700      1900       2200  
4     3       Wednesday   0000       0800       NULL      NULL      NULL       NULL  
5     3       Thursday    0800       1200       NULL      NULL      NULL       NULL  
6     3        Friday     0400       0800       0900      1600      NULL       NULL  

The Time fields are CHAR(4) since I am storing the time in a military format.

What I need to accomplish is this; for any given ClientID, insert one or more records into a schedule table with the time value of the record being within the time frames in tbl1. For example, scheduling ClientID 3 on Tuesday, the time scheduled could be 1120.

In the event that multiple records need to be inserted, the times scheduled should not be any closer than one hour.

Any and all help is appreciated!

Answer 1

First of all you may want to try a schema like

tbl_sched_avail
PK id INT
FK client_id INT
day INT (1-7)
avail_start varchar(4)
avail_end varchar(4)

This way you are not limited to a finite number of time fences.

As far as checking the schedules availability -

CREATE PROCEDURE sp_ins_sched
@start_time varchar(4),
@end_time varchar(4),
@client_id INT,
@day INT
AS
BEGIN

    DECLARE @can_create BIT
    SET @can_create = 0
    DECLARE @fence_start INT
    DECLARE @fence_end INT

    --IS DESIRED TIME WITHIN FENCE FOR CLIENT
    DECLARE c CURSOR FOR
    SELECT avail_start, avail_end FROM tbl_sched_avail
    WHERE client_id = @client_id
    AND day = @day

    OPEN c

    FETCH NEXT FROM c
    INTO @fence_start, @fence_end

    WHILE @@FETCH_STATUS = 0 AND @can_create = 0
    BEGIN
        IF @start_time >= @fence_start AND @start_time < @fence_end
            AND @end_time > @fence_start AND <= @fence_end
            SET @can_create = 1

        FETCH NEXT FROM c
        INTO @fence_start, @fence_end
    END

    CLOSE c
    DEALLOCATE c

    IF @can_create = 1
    BEGIN
        --insert your schedule here
    END

END

As far as the code for actually inserting the record I would need to know more about the tables in the database.

Answer 2

Here's my best guess as to what you're trying to do. The first two parts of the CTE are really just to get things into a form similar to what FlyingStreudel suggests. Ideally, you should change the database to match that format instead of doing this through CTEs. That will make this significantly simpler and is better for data integrity as well.

Next, I just get the distinct start times in hour increments. You could do that by joining to a Numbers table as well if you can't use CTEs (you didn't mention the version of SQL Server that you're using).

Finally, I grab one of those start times at random, using the RAND function and ROW_NUMBER. You'll want to set a good seed value for RAND().

;WITH TimesAsTimes AS
(
    SELECT
        ScheduleDay,
        CAST(SUBSTRING(T1.Time1Start, 1, 2) + ':' + SUBSTRING(T1.Time1Start, 3, 2) AS TIME) AS time_start,
        CAST(SUBSTRING(T1.Time1Stop, 1, 2) + ':' + SUBSTRING(T1.Time1Stop, 3, 2) AS TIME) AS time_stop
    FROM
        tbl1 T1
    WHERE
        T1.Time1Start IS NOT NULL
    UNION ALL
    SELECT
        ScheduleDay,
        CAST(SUBSTRING(T2.Time2Start, 1, 2) + ':' + SUBSTRING(T2.Time2Start, 3, 2) AS TIME) AS time_start,
        CAST(SUBSTRING(T2.Time2Stop, 1, 2) + ':' + SUBSTRING(T2.Time2Stop, 3, 2) AS TIME) AS time_stop
    FROM
        tbl1 T2
    WHERE
        T2.Time2Start IS NOT NULL
    UNION ALL
    SELECT
        ScheduleDay,
        CAST(SUBSTRING(T3.Time3Start, 1, 2) + ':' + SUBSTRING(T3.Time3Start, 3, 2) AS TIME) AS time_start,
        CAST(SUBSTRING(T3.Time3Stop, 1, 2) + ':' + SUBSTRING(T3.Time3Stop, 3, 2) AS TIME) AS time_stop
    FROM
        tbl1 T3
    WHERE
        T3.Time3Start IS NOT NULL
),
PossibleTimeStarts AS
(
    SELECT
        ScheduleDay,
        time_start,
        time_stop
    FROM
        TimesAsTimes TAT
    UNION ALL
    SELECT
        ScheduleDay,
        DATEADD(hh, 1, time_start) AS time_start,
        time_stop
    FROM
        PossibleTimeStarts PTS
    WHERE
        DATEADD(hh, 1, time_start) <= DATEADD(hh, -1, PTS.time_stop)
),
PossibleTimesWithRowNums AS
(
    SELECT
        ScheduleDay,
        time_start,
        ROW_NUMBER() OVER(PARTITION BY ScheduleDay ORDER BY ScheduleDay, time_start) AS row_num,
        COUNT(*) OVER(PARTITION BY ScheduleDay) AS num_rows
    FROM
        PossibleTimeStarts
)
SELECT
    *
FROM
    PossibleTimesWithRowNums
WHERE
    row_num = FLOOR(RAND() * num_rows) + 1