Sql-server – Adding time slots through trigger SQL Server

database-designsql serversql-server-2008trigger

I am quite new to SQL. I am now working on something to schedule appointments.
I found an existing answer about time slots, but did not quite understand.

I need to automatically add time slots to the timeslots table according to the availability table. if availability from start at 10:00 and end at 16:00, and duration is 60 minutes, I need to insert 6 time slot rows.

Would you please be kind enough to let me know what I should do?
Do I need to do it through a trigger? Or do I need to redesign my schema?

My trigger is like this, apparently not really working:

    CREATE TRIGGER trigger_addSlots ON availability
    FOR INSERT AS
    BEGIN
        declare @AvailabilityId int
        declare @SlotStart DateTime
        declare @SlotEnd DateTime
        declare @NumberOfSlots int
        declare @Duration int
        declare @SlotDoctorId int
        declare @i int

        Select @AvailabilityId = Id from inserted
        Select @SlotDoctorId =DoctorId from inserted
        Select @SlotStart =AvailableFrom from inserted
        Select @SlotEnd = AvailableTo from inserted 
        Select @Duration = AppointmentDuration from inserted

        set @NumberOfSlots = convert (int, (@SlotEnd - @SlotStart))/@Duration

        set @i = 0;
        While @i<@NumberOfSlots
          Begin

            insert into timeslots(AvailabilityId, SlotStart, SlotEnd, 
            SlotDoctorId,IsAvailable) values (@AvailabilityId, @SlotStart, 
            @SlotStart+@Duration,
            @SlotDoctorId,1)
            Set @SlotStart = @SlotEnd
            Set @SlotEnd = @SlotEnd + @Duration
            set @i=@i+1
          End

    END

schema

enter image description here

Best Answer

Your schema looks fine but you need to calculate the dates in a different way, In T-SQL you can't just add and subtract.

@NumberOfSlots calculation should be like this:

    set @NumberOfSlots = convert (int, (DATEDIFF(MINUTE, @SlotStart, @SlotEnd)))/@Duration

adding to a datetime is done like this:

    DATEADD(MINUTE, @Duration, @SlotStart)

The order in the loop needs to be changed too:

    set @i = 0;
    While @i<@NumberOfSlots
        Begin
           Set @SlotEnd = DATEADD(MINUTE, @Duration, @SlotStart)
           insert into timeslots
           (AvailabilityId, SlotStart, SlotEnd, SlotDoctorId,IsAvailable) 
           values 
           (@AvailabilityId, @SlotStart, @SlotEnd, @SlotDoctorId,1)
           Set @SlotStart = @SlotEnd

           set @i=@i+1
        End

With these changes, I think the trigger will work and give correct values, but that is a lot of work in a trigger.

I think you should consider solving this by building stored procedures doing the same, leaving the tables with data and no logic.

One stored procedure per table, that inserts a row to that table.

For 'Availability' the stored procedure could be createAvailability and the code would be very similar to what you have in the trigger, but in the loop it calls the stored procedure to create a row in Timeslots:

    CREATE PROCEDURE createAvailability 
    (
        @AvailabilityId INT, 
        @SlotStart DATETIME, 
        @SlotEnd DATETIME, 
        @SlotDoctorId INT
    ) AS
    BEGIN
        DECLARE @i INT
        DECLARE @NumberOfSlots INT 
        DECLARE @Duration INT 

        SET @NumberOfSlots = convert(INT, (DATEDIFF(MINUTE, @SlotStart,@SlotEnd))) / @Duration
        SET @i = 0;
        WHILE @i < @NumberOfSlots
        BEGIN
            SET @SlotEnd = DATEADD(MINUTE, @Duration, @SlotStart)
            EXEC createTimeslots(@AvailabilityId, @SlotStart, @SlotEnd, @SlotDoctorId, 1)
            SET @SlotStart = @SlotEnd
            SET @i = @i + 1
        END
    END
Related Question