Genetic Algoritma Function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Genetic Algoritma Function

Author

Topic

micnie_2020
Posting Yak Master

232 Posts

Posted - 2011-05-24 : 23:05:09

Dear All,

Anyone have the stored procedure or function to generate auto timetabling system.

Table
------
Teacher
Subject
Class
Period
Slot

Thank you.

Regards,
Micheale

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-24 : 23:27:48

what is that ? Sample data & expected result please

KH
[spoiler]Time is always against us[/spoiler]

micnie_2020
Posting Yak Master

232 Posts

Posted - 2011-05-25 : 01:13:29

Hi,

Basically i am looking for Genetic Algoritma Function in MSSQL which able to handle automated school timetabling. Call from SP execute. The time table for each class will automatic display in a tabular format. Teacher cannot overlap in the same period and same slot.

Teacher Table
-------------
Name Subject
Ali Math
John Physic
Kamarudin Chemistry
:
:

Subject
-------
Chemistry 10 Times (Per Week) //(2) Max Per Day
Math 8 Times
Physic 11 Times

Class
-----
1A
1B
2A
2B
2C

Slot
10 (slot)

Period
------
5 (Day)

Output:
-------

Class Mon1 Mon2 Mon3 Mon4 ... Mon10 Tue1 Tue2 .... Fri1 Fri2...Fri10
1A Ali |Math Ali | Math John|Physic

Thank you.

Regards,
Micheale

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-05-25 : 01:56:19

I'll be the first to say that SQL Server is not the appropriate place to write or run genetic algorithms. That's a front-end type problem

I wrote up a very simple GA for fun, it's on my blog. It's not for timetables, to get it to work for timetables you'd need to figure out the encoding and fitness function yourself. It's not trivial and I seriously doubt you'll find pre-written code for it, especially for SQL.
http://sqlinthewild.co.za/index.php/2011/02/22/and-now-for-a-completely-inappropriate-use-of-sql-server/

--
Gail Shaw
SQL Server MVP

micnie_2020
Posting Yak Master

232 Posts

Posted - 2011-05-25 : 04:52:15

Thank you Gail Shaw & KH Tan.

Here is my table structure
--------------------------

declare @slot int
declare @i int
declare @j int
declare @day int
declare @k varchar(1)
Declare @sSql AS NVarChar(4000)
Declare @field as varchar(5)

set @day = 5
set @j=0

if exists (select * from dbo.sysobjects where id = object_id(N'[tbsschedule]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Begin
Set @sSql = N'drop table tbsschedule'
exec sp_executesql @sSql
end

while @j<@day
begin
set @i=0
set @slot = 3
while @i<@slot
begin
if(@j=0)
set @k='a'
if @j=1
set @k='b'
if @j=2
set @k='c'
if @j=3
set @k='d'
if @j=4
set @k='e'

set @field=cast(@k as varchar)+cast(@i as varchar)
--print @field

if exists (select * from dbo.sysobjects where id = object_id(N'[tbsschedule]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Begin
--Set @sSql = N'drop table tbsschedule'
--exec sp_executesql @sSql
Set @sSql = N'Alter table [tbsschedule] add '+@field+' [varchar] (255) NULL'
--print @sSql
exec sp_executesql @sSql
End
else
Begin
Set @sSql = N'create table [tbsschedule] ( class [varchar] (20) null, '+@field+' [varchar] (255) NULL)'
--print @sSql
exec sp_executesql @sSql

end

set @i=@i + 1
--select * from tbsschedule

end

set @j=@j + 1
end

I belive SP able to do so to random insert data into above table based on class, teacher, subject.
I still trying to figure it. Yes, i knew Genetic Algoritma able to perform in C++, C, MathWork/MathLab. But i looking for advance sql to do so.

Thank you.

Regards,
Micheale

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-05-25 : 06:20:45

Good luck.

Seriously, this is not quick, it's likely to be a major piece of development work (the rough one I wrote up for that blog post took me a couple days to get right) and, quite frankly it's not something that should be done in SQL Server.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources