Scheduling movies in a database

[BenCoo]

Hello,

I have a ProgramItem-class as follow:

mItemID as Integer

mDtg as shortDateTime

mMovieID as Integer (linked to a ICollection of Movies from a table Movies)

mTheaterID as Integer (linked to a ICollection Theaters from a table
Theaters)

And a Server 2005 TSQL-tabel MovieProgram:

ID (int)
DTG (shortDateTime)
MovieID (int)
TheaterID (int)

1
22-03-2007 13:00
24
6

2
22-03-2007 14:30
13 (playtime = 02:00 hrs)
4

3
22-03-2007 15:25
68
3

4
22-03-2007 17:00
19
4

5
22-03-2007 20:00
84
3


I wish to schedule a ProgramItem (retrieved movie playtime = 01:30) on
22-03-2007 15:00 in theater 4

How can I find out that:

1: theater 4 is available at that time (in this example it is not)

2: and there is enough time to project the movie before the next planned
movie in the same theater starts (see ID 2 & 4)

I have al ready figure this out:

Dim intTimeslot As integer

Dim prgItem As ProgramItem

With prgItem

..Dtg = "22-03-2007 15:00"

..MovieId = 54

..TheaterID = 4

End With

'Here comes the code for testing if there is enough time to project the
movie, some help 'will be appreciated!

If intTimeslot = 1 then

'Movie can be planned as suggested

ScheduleMovie(p rgItem) ' save object prgItem in table MovieProgram

ElseIf intTimeslot = 2 then

'Movie can be planned but with modified Starttime (DTG)

prgItem.Dtg = '22-03-2007 hh:mm'

scheduleMovie(p rgItem)

Else

MsgBox("Show Error: theater is occupied")

End If

Thanks in advance,

Benny

[dbahooker]

you just need to figure out what a cartesian is.. get a simple
cartesian of 'every possible theater and every possible time for every
possible day'

a cartesian would allow you to see a schedule of 'what moves are
showing this week'
push it into a table and then use action queries (insert, update) in
order to push the calculated values into a table

you might be able to just use the PIVOT key word in order to do
something a whole ton simpler; it just depends on whether you're
looking for a one-time report or seomthing that will get displayed on
your website by 10 million visitors

On Feb 22, 7:08 am, "BenCoo" <teddy...@hotma il.comwrote:

Hello,

I have a ProgramItem-class as follow:

mItemID as Integer

mDtg as shortDateTime

mMovieID as Integer (linked to a ICollection of Movies from a table Movies)

mTheaterID as Integer (linked to a ICollection Theaters from a table
Theaters)

And a Server 2005 TSQL-tabel MovieProgram:

ID (int)
DTG (shortDateTime)
MovieID (int)
TheaterID (int)

1
22-03-2007 13:00
24
6

2
22-03-2007 14:30
13 (playtime = 02:00 hrs)
4

3
22-03-2007 15:25
68
3

4
22-03-2007 17:00
19
4

5
22-03-2007 20:00
84
3

I wish to schedule a ProgramItem (retrieved movie playtime = 01:30) on
22-03-2007 15:00 in theater 4

How can I find out that:

1: theater 4 is available at that time (in this example it is not)

2: and there is enough time to project the movie before the next planned
movie in the same theater starts (see ID 2 & 4)

I have al ready figure this out:

Dim intTimeslot As integer

Dim prgItem As ProgramItem

With prgItem

.Dtg = "22-03-2007 15:00"

.MovieId = 54

.TheaterID = 4

End With

'Here comes the code for testing if there is enough time to project the
movie, some help 'will be appreciated!

If intTimeslot = 1 then

'Movie can be planned as suggested

ScheduleMovie(p rgItem) ' save object prgItem in table MovieProgram

ElseIf intTimeslot = 2 then

'Movie can be planned but with modified Starttime (DTG)

prgItem.Dtg = '22-03-2007 hh:mm'

scheduleMovie(p rgItem)

Else

MsgBox("Show Error: theater is occupied")

End If

Thanks in advance,

Benny