create an automatic week subject scheduler?

To create an automatic week subject scheduler, follow these steps:

1. Frontend (HTML/CSS): Design a grid or table to display the weekly schedule. Include input fields for subjects, times, and durations.

2. JavaScript: Handle the dynamic features, like adding/editing subjects and checking for conflicts. Use JavaScript to auto-schedule based on available slots.

3. Backend (PHP/Database): Store the subjects and schedule data in a database (e.g., MySQL). Use PHP to handle logic, like preventing overlapping subjects.

4. AJAX: Use AJAX to update the schedule in real-time without refreshing the page.

I added an outer WHILE loop that continues until all sessions for a subject are allocated (`WHILE @count < @subcount`).

After inserting a new row into `tbl_week_sched`, reset the day variables (`@sday1`, `@sday2`, etc.) to `null` to ensure they don't carry over to the next row.
 Properly increment `@count` and `@count2` will track the number of sessions allocated and the number of workdays used.

USE [DB_SchoolManager]
GO
/****** Object: StoredProcedure [dbo].[Sched] Script Date: 1/22/2019 12:02:03 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[Sched]
AS
Begin
    Delete from tbl_week_sched

    Declare @classid int
    Set @classid=1

    drop table #temp1
    SELECT asnsub.[sub_id], sub.[sub_name], asnsub.[assign_hours], ws.[week_count]
    into #temp1
    FROM [DB_SchoolManager].[dbo].[tbl_sub_cor_assigns] asnsub
    INNER JOIN [DB_SchoolManager].[dbo].[tbl_subject] sub ON asnsub.[sub_id]=sub.[sub_id]
    INNER JOIN [DB_SchoolManager].[dbo].[tbl_classes] cls ON asnsub.[regcor_id]=cls.[cor_id]
    LEFT JOIN [DB_SchoolManager].[dbo].[tbl_week_session_count] ws ON asnsub.[sub_id]=ws.[sub_id]
    WHERE cls.[class_id]=@classid

    Declare @weekstart date
    Declare @weekend date
    Declare @workdays int
    Set @weekstart='2019-01-20';
    Set @weekend='2019-01-26';
    Set @workdays=0;

    Declare @day1 date
    Declare @day2 date
    Declare @day3 date
    Declare @day4 date
    Declare @day5 date
    Declare @day6 date
    Declare @day7 date

    if (@weekstart Not in (SELECT [date] FROM [tbl_holidaies])) Begin Set @day1=@weekstart; Set @workdays=1 END else Begin Set @day1=null End;
    if ((DATEADD(dd, 1, @weekstart)) Not in (SELECT [date] FROM [tbl_holidaies])) Begin Set @day2=(DATEADD(dd, 1, @weekstart)); Set @workdays=@workdays+1; END else Begin Set @day2=null End;
    if ((DATEADD(dd, 2, @weekstart)) Not in (SELECT [date] FROM [tbl_holidaies])) Begin Set @day3=(DATEADD(dd, 2, @weekstart)); Set @workdays=@workdays+1; END else Begin Set @day3=null End;
    if ((DATEADD(dd, 3, @weekstart)) Not in (SELECT [date] FROM [tbl_holidaies])) Begin Set @day4=(DATEADD(dd, 3, @weekstart)); Set @workdays=@workdays+1; END else Begin Set @day4=null End;
    if ((DATEADD(dd, 4, @weekstart)) Not in (SELECT [date] FROM [tbl_holidaies])) Begin Set @day5=(DATEADD(dd, 4, @weekstart)); Set @workdays=@workdays+1; END else Begin Set @day5=null End;
    if ((DATEADD(dd, 5, @weekstart)) Not in (SELECT [date] FROM [tbl_holidaies])) Begin Set @day6=(DATEADD(dd, 5, @weekstart)); Set @workdays=@workdays+1; END else Begin Set @day6=null End;
    if (@weekend Not in (SELECT [date] FROM [tbl_holidaies])) Begin Set @day7=@weekend; Set @workdays=@workdays+1; END else Begin Set @day7=null End;

    Insert Into tbl_week_sched ([1],[2],[3],[4],[5],[6],[7]) values (@day1,@day2,@day3,@day4,@day5,@day6,@day7)

    Declare @subid int
    Declare @subname nvarchar(50)
    Declare @subcount int
    Declare @count int
    Declare @count2 int
    Declare @sday1 nvarchar(50)
    Declare @sday2 nvarchar(50)
    Declare @sday3 nvarchar(50)
    Declare @sday4 nvarchar(50)
    Declare @sday5 nvarchar(50)
    Declare @sday6 nvarchar(50)
    Declare @sday7 nvarchar(50)

    Set @count =0
    Set @count2=0

    DECLARE MY_CURSOR CURSOR
    LOCAL STATIC READ_ONLY FORWARD_ONLY
    FOR
    SELECT DISTINCT sub_id
    FROM #temp1

    OPEN MY_CURSOR
    FETCH NEXT FROM MY_CURSOR INTO @subid
    WHILE @@FETCH_STATUS = 0
    BEGIN
        Set @subname=(select sub_name from tbl_subject where sub_id=@subid)
        Set @subcount=(select week_count from tbl_week_session_count where sub_id=@subid)

        WHILE @count < @subcount
        BEGIN
            if ((Select TOP (1) [1] from tbl_week_sched) IS NOT null)
            Begin
                if (@count2<@workdays)
                Begin
                    if (@count<@subcount)
                    Begin
                        Set @sday1= @subname;
                        Set @count=@count+1;
                    end
                    ELSE
                    Begin
                        Set @sday1=null
                    End
                    Set @count2=@count2+1;
                End
                Else
                Begin
                    Insert Into tbl_week_sched ([1],[2],[3],[4],[5],[6],[7]) values (@sday1,@sday2,@sday3,@sday4,@sday5,@sday6,@sday7);
                    Set @count2=0;
                    Set @sday1=null; Set @sday2=null; Set @sday3=null; Set @sday4=null; Set @sday5=null; Set @sday6=null; Set @sday7=null;
                End
            End

            if ((Select TOP (1) [2] from tbl_week_sched) IS NOT null)
            Begin
                if (@count2<@workdays)
                Begin
                    if (@count<@subcount)
                    Begin
                        Set @sday2= @subname;
                        Set @count=@count+1;
                    end
                    ELSE
                    Begin
                        Set @sday2=null
                    End
                    Set @count2=@count2+1;
                End
                Else
                Begin
                    Insert Into tbl_week_sched ([1],[2],[3],[4],[5],[6],[7]) values (@sday1,@sday2,@sday3,@sday4,@sday5,@sday6,@sday7);
                    Set @count2=0;
                    Set @sday1=null; Set @sday2=null; Set @sday3=null; Set @sday4=null; Set @sday5=null; Set @sday6=null; Set @sday7=null;
                End
            End

            if ((Select TOP (1) [3] from tbl_week_sched) IS NOT null)
            Begin
                if (@count2<@workdays)
                Begin
                    if (@count<@subcount)
                    Begin
                        Set @sday3= @subname;
                        Set @count=@count+1;
                    end
                    ELSE
                    Begin
                        Set @sday3=null
                    End
                    Set @count2=@count2+1;
                End
                Else
                Begin
                    Insert Into tbl_week_sched ([1],[2],[3],[4],[5],[6],[7]) values (@sday1,@sday2,@sday3,@sday4,@sday5,@sday6,@sday7);
                    Set @count2=0;
                    Set @sday1=null; Set @sday2=null; Set @sday3=null; Set @sday4=null; Set @sday5=null; Set @sday6=null; Set @sday7=null;
                End
            End

            if ((Select TOP (1) [4] from tbl_week_sched) IS NOT null)
            Begin
                if (@count2<@workdays)
                Begin
                    if (@count<@subcount)
                    Begin
                        Set @sday4= @subname;
                        Set @count=@count+1;
                    end
                    ELSE
                    Begin
                        Set @sday4=null
                    End
                    Set @count2=@count2+1;
                End
                Else
                Begin
                    Insert Into tbl_week_sched ([1],[2],[3],[4],[5],[6],[7]) values (@sday1,@sday2,@sday3,@sday4,@sday5,@sday6,@sday7);
                    Set @count2=0;
                    Set @sday1=null; Set @sday2=null; Set @sday3=null; Set @sday4=null; Set @sday5=null; Set @sday6=null; Set @sday7=null;
                End
            End

            if ((Select TOP (1) [5] from tbl_week_sched) IS NOT null)
            Begin
                if (@count2<@workdays)
                Begin
                    if (@count<@subcount)
                    Begin
                        Set @sday5= @subname;
                        Set @count=@count+1;
                    end
                    ELSE
                    Begin
                        Set @sday5=null
                    End
                    Set @count2=@count2+1;
                End
                Else
                Begin
                    Insert Into tbl_week_sched ([1],[2],[3],[4],[5],[6],[7]) values (@sday1,@sday2,@sday3,@sday4,@sday5,@sday6,@sday7);
                    Set @count2=0;
                    Set @sday1=null; Set @sday2=null; Set @sday3=null; Set @sday4=null; Set @sday5=null; Set @sday6=null; Set @sday7=null;
                End
            End

            if ((Select TOP (1) [6] from tbl_week_sched) IS NOT null)
            Begin
                if (@count2<@workdays)
                Begin
                    if (@count<@subcount)
                    Begin
                        Set @sday6= @subname;
                        Set @count=@count+1;
                    end
                    ELSE
                    Begin
                        Set @sday6=null
                    End
                    Set @count2=@count2+1;
                End
                Else
                Begin
                    Insert Into tbl_week_sched ([1],[2],[3],[4],[5],[6],[7]) values (@sday1,@sday2,@sday3,@sday4,@sday5,@sday6,@sday7);
                    Set @count2=0;
                    Set @sday1=null; Set @sday2=null; Set @sday3=null; Set @sday4=null; Set @sday5=null; Set @sday6=null; Set @sday7=null;
                End
            End

            if ((Select TOP (1) [7] from tbl_week_sched) IS NOT null)
            Begin
                if (@count2<@workdays)
                Begin
                    if (@count<@subcount)
                    Begin
                        Set @sday7= @subname;
                        Set @count=@count+1;
                    end
                    ELSE
                    Begin
                        Set @sday7=null
                    End
                    Set @count2=@count2+1;
                End
                Else
                Begin
                    Insert Into tbl_week_sched ([1],[2],[3],[4],[5],[6],[7]) values (@sday1,@sday2,@sday3,@sday4,@sday5,@sday6,@sday7);
                    Set @count2=0;
                    Set @sday1=null; Set @sday2=null; Set @sday3=null; Set @sday4=null; Set @sday5=null; Set @sday6=null; Set @sday7=null;
                End
            End
        END

        FETCH NEXT FROM MY_CURSOR INTO @subid
    END

    CLOSE MY_CURSOR
    DEALLOCATE MY_CURSOR

    SELECT * FROM [DB_SchoolManager].[dbo].[tbl_week_sched]
End