create an automatic week subject scheduler?

Question

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
print @workdays
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)
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 @count=0;
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 @count=0;
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 @count=0;
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 @count=0;
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 @count=0;
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 @count=0;
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 @count=0;
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

#temp1

 

sub_idsub_nameassign_hoursweek_count

2English1354

1Arabic408

3English2503

4Math504

5History202

6Geography202

7Philosophy202

8Physics402

9Chemistry402

10Biology402

 

But the results 

 

id1234567

11034NULL2019-01-212019-01-222019-01-232019-01-242019-01-25NULL

11035NULLArabicArabicArabicArabicArabicNULL

11036NULLNULLEnglish1English1English1English1NULL

11037NULLMathNULLEnglish2English2English2NULL

11038NULLNULLNULLNULLMathMathNULL

11039NULLGeographyNULLNULLNULLHistoryNULL

11040NULLPhilosophyPhilosophyNULLNULLNULLNULL

11041NULLNULLPhysicsPhysicsNULLNULLNULL

11042NULLNULLNULLChemistryChemistryNULLNULL

 

the problem when the loop reach day 7 and the subject count still not completed it does not create a new line
as you see arabic count is 5 but must add new line below with day 1,2,3 is arabic