I have the following tables:
AG02
AG07
AG08
AG25
The table AG02 contains every schedule possible example:
hr_ini hr_fim cd_rgr
07:00 07:05 1
07:05 07:10 2
07:10 07:15 3
07:15 07:20 4
07:20 07:25 5
07:25 07:30 6
07:30 07:35 7
07:35 07:40 8
07:40 09:45 9
07:45 08:00 10
08:00 08:05 11
08:05 08:10 12
08:10 08:15 13
08:15 08:20 14
08:20 08:25 15
08:25 08:30 16
08:30 08:35 17
08:35 08:40 18
08:45 08:50 19
08:50 08:55 20
08:55 09:00 21
the table ag07 has all possible exams (cd_proc), like this if certain exam lasts 0:30 hs (half hour) and it begins at 07:00 o'clock
it contains the following fields
cd_rgr cd_loc cd_proc
1 2 ABDS
2 2 ABDS
3 2 ABDS
4 2 ABDS
5 2 ABDS
6 2 ABDS
if I have more schedules, for instance 07:30 at 09:00 o'clock and I place every possible schedule including the following registrations in cd_rgr (3,4,5,6,7,8,9,10., ..21), if I vary the room, for instance room 3 I also place all possible schedules for this room.
The table AG08 already contains all exams agendados, and the principal campos(primary key) they are:
data_ini annotates
26/04/2000 1
26/04/2000 2
26/04/2000 3
the field annotates (the patient's code) it is to call with the table AG25 through this field with same name, and the other field of the table AG25 is cd_rgr, like this to mark an exam, for instance ABDS, at 07:00 o'clock the fields of the table AG25 would be like this:
cd_rgr annotates
1 1
2 1
.. 1
.. 1
.. 1
6 1
The problem is the following: I need to show every schedule in a grid (or any better control), where the user types the date of the exam, the exam that he wants to do, and I have to show every schedule, and in certain column to show that schedule is busy or not. I would set up the schedule according to the duration of the exam, for instance the exam ABDS lasts mei-hour the table he/she would have to be set up of stocking in half hour
For instance the user typed 26//04/2000, and suppose that some schedules are busy, I would need to show like this:
hr_ini hr_fim
07:00 07:30 X
07:30 08:00
08:00 08:30
08:30 09:00
09:00 09:30 X
08:30 10:00
10:00 10:30
10:30 11:00
11:00 11:30
11:30 12:00
To get to diffuse the free schedules I already got with the following command,
select ag02_hr_ini,
ag02_hr_fim,
ag07_cd_loc,
ag07_cd_proc,
ag07_cd_rgr
from ag02
left join ag07
on ag02_cd_rgr = ag07_cd_rgr
where ag02_cd_rgr not in (select ag25_cd_rgr from
ag25,ag08 where ag25_cd_ficha = ag08_cd_ficha
and ag08_dth_ini = '2000-04-26') and ag07_cd_loc is not null
I get to bring all schedules certain date, but without the schedules already marked, which I want it is every schedule and some sign of the schedules already marked
