SQLTeam.com | Weblogs | Forums

Course dates generator on the basis of description

I have a table @course_schedule where all course name and schedule of classes is listed. There 2 parent courses and all other courses are derived from that having some changes in schedule.

Parent courses are very basic course and classes are on daily basis except weekends and holidays.

All the valid class dates for parent are in table @parent_course_dates, there could be some change in holidays pattern so both parent courses valid dates are captured.

There is one more table @alldates where dates data is captured with dayname.

I want to create logic that will generate

lecture_dates lab_dates doubt_session_date Any_other_date

As per description column mentioned in table @course_schedule for each date type. Dates will follow parent course schedule for all weekday (monday-friday) dates schedule and @all_dates table for any weekend dates (saturday).

If any weekday (mon-friday) is not availble in @parent_course_dates table that means that day would be holiday and should not be considered for class in this case next or previous date should be picked up on the basis of description i.e if description is Monday after third thursday , and monday is holiday then tuesday should be picked if tuesday is again a holiday then wednesday should be picked.

In case of definition like wednesday before third thursday, and wednesday is holiday then tuesday should be picked.

In case of saturday there must not be any impact of holiday.

Following is sample data.

     declare @course_schedule table (id int, course_name varchar(100), parent_course_id int, course_type char(5),
     lecture_schedule_desc varchar(500), lab_schedule_desc varchar(500),doubts_session_desc varchar(100), 
     active_in_months varchar(100) )

     declare @parent_course_dates table (id int identity, course_id int , valid_date int )

     declare @alldates table (valid_date int,[dayname] varchar(100))

     insert into @course_schedule 
     values(101, 'dax_course', 1, 'c1','Monday after third thursday of month in active_in_months'
     ,'wednesday before third thursday of month in active_in_months' ,'saturday after second friday of month in active_in_months' ,'1,2,3')

     insert into @course_schedule 
     values(111, 'dax2_course', 1, 'c1','Tuesday after second thursday of month in active_in_months'
     ,'monday after third friday of month in active_in_months' ,'Friday after second monday of month in active_in_months' ,'3,4')

     insert into @course_schedule 
     values(201, 'ml_course', 35, 'c1','Monday after third thursday of month in active_in_months'
     ,'wednesday before third thursday of month in active_in_months' ,'saturday after second monday of month in active_in_months' ,'1,2')


    insert into @parent_course_dates values('1 ','20190102')
    insert into @parent_course_dates values('1 ','20190103')
    insert into @parent_course_dates values('1 ','20190104')
    insert into @parent_course_dates values('1 ','20190107')
    insert into @parent_course_dates values('1 ','20190108')
    insert into @parent_course_dates values('1 ','20190109')
    insert into @parent_course_dates values('1 ','20190110')
    insert into @parent_course_dates values('1 ','20190111')
    insert into @parent_course_dates values('1 ','20190114')
    insert into @parent_course_dates values('1 ','20190115')
    insert into @parent_course_dates values('1 ','20190116')
    insert into @parent_course_dates values('1 ','20190117')
    insert into @parent_course_dates values('1 ','20190118')
    insert into @parent_course_dates values('1 ','20190121')
    insert into @parent_course_dates values('1 ','20190122')
    insert into @parent_course_dates values('1 ','20190123')
    insert into @parent_course_dates values('1 ','20190124')
    insert into @parent_course_dates values('1 ','20190125')
    insert into @parent_course_dates values('1 ','20190128')
    insert into @parent_course_dates values('1 ','20190129')
    insert into @parent_course_dates values('1 ','20190130')
    insert into @parent_course_dates values('1 ','20190131')
    insert into @parent_course_dates values('1 ','20190201')
    insert into @parent_course_dates values('1 ','20190204')
    insert into @parent_course_dates values('1 ','20190205')
    insert into @parent_course_dates values('1 ','20190206')
    insert into @parent_course_dates values('1 ','20190207')
    insert into @parent_course_dates values('1 ','20190208')
    insert into @parent_course_dates values('1 ','20190211')
    insert into @parent_course_dates values('1 ','20190212')
    insert into @parent_course_dates values('1 ','20190213')
    insert into @parent_course_dates values('1 ','20190214')
    insert into @parent_course_dates values('1 ','20190215')
    insert into @parent_course_dates values('1 ','20190218')
    insert into @parent_course_dates values('1 ','20190219')
    insert into @parent_course_dates values('1 ','20190220')
    insert into @parent_course_dates values('1 ','20190221')
    insert into @parent_course_dates values('1 ','20190222')
    insert into @parent_course_dates values('1 ','20190225')
    insert into @parent_course_dates values('1 ','20190226')
    insert into @parent_course_dates values('1 ','20190227')
    insert into @parent_course_dates values('1 ','20190228')
    insert into @parent_course_dates values('1 ','20190301')
    insert into @parent_course_dates values('1 ','20190304')
    insert into @parent_course_dates values('1 ','20190305')
    insert into @parent_course_dates values('1 ','20190306')
    insert into @parent_course_dates values('1 ','20190307')
    insert into @parent_course_dates values('1 ','20190308')
    insert into @parent_course_dates values('1 ','20190311')
    insert into @parent_course_dates values('1 ','20190312')
    insert into @parent_course_dates values('1 ','20190313')
    insert into @parent_course_dates values('1 ','20190314')
    insert into @parent_course_dates values('1 ','20190315')
    insert into @parent_course_dates values('1 ','20190318')
    insert into @parent_course_dates values('1 ','20190319')
    insert into @parent_course_dates values('1 ','20190320')
    insert into @parent_course_dates values('1 ','20190321')
    insert into @parent_course_dates values('1 ','20190322')
    insert into @parent_course_dates values('1 ','20190325')
    insert into @parent_course_dates values('1 ','20190326')
    insert into @parent_course_dates values('1 ','20190327')
    insert into @parent_course_dates values('1 ','20190328')
    insert into @parent_course_dates values('1 ','20190329')
    insert into @parent_course_dates values('1 ','20190401')
    insert into @parent_course_dates values('1 ','20190402')
    insert into @parent_course_dates values('1 ','20190403')
    insert into @parent_course_dates values('1 ','20190404')
    insert into @parent_course_dates values('1 ','20190405')
    insert into @parent_course_dates values('1 ','20190408')
    insert into @parent_course_dates values('1 ','20190409')
    insert into @parent_course_dates values('1 ','20190410')
    insert into @parent_course_dates values('1 ','20190411')
    insert into @parent_course_dates values('1 ','20190412')
    insert into @parent_course_dates values('1 ','20190415')
    insert into @parent_course_dates values('1 ','20190416')
    insert into @parent_course_dates values('1 ','20190417')
    insert into @parent_course_dates values('1 ','20190418')
    insert into @parent_course_dates values('1 ','20190423')
    insert into @parent_course_dates values('1 ','20190424')
    insert into @parent_course_dates values('1 ','20190425')
    insert into @parent_course_dates values('1 ','20190426')
    insert into @parent_course_dates values('1 ','20190429')
    insert into @parent_course_dates values('1 ','20190430')
    insert into @parent_course_dates values('1 ','20191231')
    insert into @parent_course_dates values('35','20190102')
    insert into @parent_course_dates values('35','20190103')
    insert into @parent_course_dates values('35','20190104')
    insert into @parent_course_dates values('35','20190107')
    insert into @parent_course_dates values('35','20190108')
    insert into @parent_course_dates values('35','20190109')
    insert into @parent_course_dates values('35','20190110')
    insert into @parent_course_dates values('35','20190111')
    insert into @parent_course_dates values('35','20190114')
    insert into @parent_course_dates values('35','20190115')
    insert into @parent_course_dates values('35','20190116')
    insert into @parent_course_dates values('35','20190117')
    insert into @parent_course_dates values('35','20190118')
    insert into @parent_course_dates values('35','20190121')
    insert into @parent_course_dates values('35','20190122')
    insert into @parent_course_dates values('35','20190123')
    insert into @parent_course_dates values('35','20190124')
    insert into @parent_course_dates values('35','20190125')
    insert into @parent_course_dates values('35','20190128')
    insert into @parent_course_dates values('35','20190129')
    insert into @parent_course_dates values('35','20190130')
    insert into @parent_course_dates values('35','20190131')
    insert into @parent_course_dates values('35','20190201')
    insert into @parent_course_dates values('35','20190204')
    insert into @parent_course_dates values('35','20190205')
    insert into @parent_course_dates values('35','20190206')
    insert into @parent_course_dates values('35','20190207')
    insert into @parent_course_dates values('35','20190208')
    insert into @parent_course_dates values('35','20190211')
    insert into @parent_course_dates values('35','20190212')
    insert into @parent_course_dates values('35','20190213')
    insert into @parent_course_dates values('35','20190214')
    insert into @parent_course_dates values('35','20190215')
    insert into @parent_course_dates values('35','20190218')
    insert into @parent_course_dates values('35','20190219')
    insert into @parent_course_dates values('35','20190220')
    insert into @parent_course_dates values('35','20190221')
    insert into @parent_course_dates values('35','20190222')
    insert into @parent_course_dates values('35','20190225')
    insert into @parent_course_dates values('35','20190226')
    insert into @parent_course_dates values('35','20190227')
    insert into @parent_course_dates values('35','20190228')
    insert into @parent_course_dates values('35','20190301')
    insert into @parent_course_dates values('35','20190304')
    insert into @parent_course_dates values('35','20190305')
    insert into @parent_course_dates values('35','20190306')
    insert into @parent_course_dates values('35','20190307')
    insert into @parent_course_dates values('35','20190308')
    insert into @parent_course_dates values('35','20190311')
    insert into @parent_course_dates values('35','20190312')
    insert into @parent_course_dates values('35','20190313')
    insert into @parent_course_dates values('35','20190314')
    insert into @parent_course_dates values('35','20190315')
    insert into @parent_course_dates values('35','20190318')
    insert into @parent_course_dates values('35','20190319')
    insert into @parent_course_dates values('35','20190320')
    insert into @parent_course_dates values('35','20190321')
    insert into @parent_course_dates values('35','20190322')
    insert into @parent_course_dates values('35','20190325')
    insert into @parent_course_dates values('35','20190326')
    insert into @parent_course_dates values('35','20190327')
    insert into @parent_course_dates values('35','20190328')
    insert into @parent_course_dates values('35','20190329')
    insert into @parent_course_dates values('35','20190401')
    insert into @parent_course_dates values('35','20190402')
    insert into @parent_course_dates values('35','20190403')
    insert into @parent_course_dates values('35','20190404')
    insert into @parent_course_dates values('35','20190405')
    insert into @parent_course_dates values('35','20190408')
    insert into @parent_course_dates values('35','20190409')
    insert into @parent_course_dates values('35','20190410')
    insert into @parent_course_dates values('35','20190411')
    insert into @parent_course_dates values('35','20190412')
    insert into @parent_course_dates values('35','20190415')
    insert into @parent_course_dates values('35','20190416')
    insert into @parent_course_dates values('35','20190417')
    insert into @parent_course_dates values('35','20190418')
    insert into @parent_course_dates values('35','20190422')
    insert into @parent_course_dates values('35','20190423')
    insert into @parent_course_dates values('35','20190424')
    insert into @parent_course_dates values('35','20190425')
    insert into @parent_course_dates values('35','20190426')
    insert into @parent_course_dates values('35','20190429')
    insert into @parent_course_dates values('35','20190430')

    insert into @alldates values ('20190101','Tuesday')
    insert into @alldates values ('20190102','Wednesday')
    insert into @alldates values ('20190103','Thursday')
    insert into @alldates values ('20190104','Friday')
    insert into @alldates values ('20190105','Saturday')
    insert into @alldates values ('20190106','Sunday')
    insert into @alldates values ('20190107','Monday')
    insert into @alldates values ('20190108','Tuesday')
    insert into @alldates values ('20190109','Wednesday')
    insert into @alldates values ('20190110','Thursday')
    insert into @alldates values ('20190111','Friday')
    insert into @alldates values ('20190112','Saturday')
    insert into @alldates values ('20190113','Sunday')
    insert into @alldates values ('20190114','Monday')
    insert into @alldates values ('20190115','Tuesday')
    insert into @alldates values ('20190116','Wednesday')
    insert into @alldates values ('20190117','Thursday')
    insert into @alldates values ('20190118','Friday')
    insert into @alldates values ('20190119','Saturday')
    insert into @alldates values ('20190120','Sunday')
    insert into @alldates values ('20190121','Monday')
    insert into @alldates values ('20190122','Tuesday')
    insert into @alldates values ('20190123','Wednesday')
    insert into @alldates values ('20190124','Thursday')
    insert into @alldates values ('20190125','Friday')
    insert into @alldates values ('20190126','Saturday')
    insert into @alldates values ('20190127','Sunday')
    insert into @alldates values ('20190128','Monday')
    insert into @alldates values ('20190129','Tuesday')
    insert into @alldates values ('20190130','Wednesday')
    insert into @alldates values ('20190131','Thursday')
    insert into @alldates values ('20190201','Friday')
    insert into @alldates values ('20190202','Saturday')
    insert into @alldates values ('20190203','Sunday')
    insert into @alldates values ('20190204','Monday')
    insert into @alldates values ('20190205','Tuesday')
    insert into @alldates values ('20190206','Wednesday')
    insert into @alldates values ('20190207','Thursday')
    insert into @alldates values ('20190208','Friday')
    insert into @alldates values ('20190209','Saturday')
    insert into @alldates values ('20190210','Sunday')
    insert into @alldates values ('20190211','Monday')
    insert into @alldates values ('20190212','Tuesday')
    insert into @alldates values ('20190213','Wednesday')
    insert into @alldates values ('20190214','Thursday')
    insert into @alldates values ('20190215','Friday')
    insert into @alldates values ('20190216','Saturday')
    insert into @alldates values ('20190217','Sunday')
    insert into @alldates values ('20190218','Monday')
    insert into @alldates values ('20190219','Tuesday')
    insert into @alldates values ('20190220','Wednesday')
    insert into @alldates values ('20190221','Thursday')
    insert into @alldates values ('20190222','Friday')
    insert into @alldates values ('20190223','Saturday')
    insert into @alldates values ('20190224','Sunday')
    insert into @alldates values ('20190225','Monday')
    insert into @alldates values ('20190226','Tuesday')
    insert into @alldates values ('20190227','Wednesday')
    insert into @alldates values ('20190228','Thursday')
    insert into @alldates values ('20190301','Friday')
    insert into @alldates values ('20190302','Saturday')
    insert into @alldates values ('20190303','Sunday')
    insert into @alldates values ('20190304','Monday')
    insert into @alldates values ('20190305','Tuesday')
    insert into @alldates values ('20190306','Wednesday')
    insert into @alldates values ('20190307','Thursday')
    insert into @alldates values ('20190308','Friday')
    insert into @alldates values ('20190309','Saturday')
    insert into @alldates values ('20190310','Sunday')
    insert into @alldates values ('20190311','Monday')
    insert into @alldates values ('20190312','Tuesday')
    insert into @alldates values ('20190313','Wednesday')
    insert into @alldates values ('20190314','Thursday')
    insert into @alldates values ('20190315','Friday')
    insert into @alldates values ('20190316','Saturday')
    insert into @alldates values ('20190317','Sunday')
    insert into @alldates values ('20190318','Monday')
    insert into @alldates values ('20190319','Tuesday')
    insert into @alldates values ('20190320','Wednesday')
    insert into @alldates values ('20190321','Thursday')
    insert into @alldates values ('20190322','Friday')
    insert into @alldates values ('20190323','Saturday')
    insert into @alldates values ('20190324','Sunday')
    insert into @alldates values ('20190325','Monday')
    insert into @alldates values ('20190326','Tuesday')
    insert into @alldates values ('20190327','Wednesday')
    insert into @alldates values ('20190328','Thursday')
    insert into @alldates values ('20190329','Friday')
    insert into @alldates values ('20190330','Saturday')
    insert into @alldates values ('20190331','Sunday')
    insert into @alldates values ('20190401','Monday')
    insert into @alldates values ('20190402','Tuesday')
    insert into @alldates values ('20190403','Wednesday')
    insert into @alldates values ('20190404','Thursday')
    insert into @alldates values ('20190405','Friday')
    insert into @alldates values ('20190406','Saturday')
    insert into @alldates values ('20190407','Sunday')
    insert into @alldates values ('20190408','Monday')
    insert into @alldates values ('20190409','Tuesday')
    insert into @alldates values ('20190410','Wednesday')
    insert into @alldates values ('20190411','Thursday')
    insert into @alldates values ('20190412','Friday')
    insert into @alldates values ('20190413','Saturday')
    insert into @alldates values ('20190414','Sunday')
    insert into @alldates values ('20190415','Monday')
    insert into @alldates values ('20190416','Tuesday')
    insert into @alldates values ('20190417','Wednesday')
    insert into @alldates values ('20190418','Thursday')
    insert into @alldates values ('20190419','Friday')
    insert into @alldates values ('20190420','Saturday')
    insert into @alldates values ('20190421','Sunday')
    insert into @alldates values ('20190422','Monday')
    insert into @alldates values ('20190423','Tuesday')
    insert into @alldates values ('20190424','Wednesday')
    insert into @alldates values ('20190425','Thursday')
    insert into @alldates values ('20190426','Friday')
    insert into @alldates values ('20190427','Saturday')
    insert into @alldates values ('20190428','Sunday')
    insert into @alldates values ('20190429','Monday')
    insert into @alldates values ('20190430','Tuesday')