select date in each month, given dayname

tenma-tenma · #1 (**permalink**) 06-09-07, 05:01

hi...
i would like to select every date in particular month that have appointment for that day. besides, i also need to select every date, that a doctor works in that month (based on the dayname, for example, is Monday)
thus, if a doctor works every Tuesday and Saturday, then for June 2007, the date will be 5, 9, 12, 16, 19, 23, 26, 30.. how could i do that?

i have this one for selecting the appointment:

Code:

SELECT DAY(a.date) as day, COUNT(a.appId) FROM appointment a, staff s WHERE MONTH(a.date) = '$month' AND YEAR(a.date) = '$year' and s.staffId= '$staffId' and a.staffId= s.staffId GROUP BY day

however, i'm quite confuse for selecting the date for every given dayname..

thank's for your attention

r937 · #2 (**permalink**) 06-09-07, 07:10

this solution requires an integers table

Code:

create table integers (i integer not null primary key);
insert into integers (i) values
(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

okay, now we generate the numbers from 0 to 30 like this --

Code:

select 10*t.i+u.i as d
  from integers as t
cross
  join integers as u
 where 10*t.i+u.i between 0 and 30

this is certainly not the only way to generate the numbers 0 through 30, so feel free to experiment, but i like the integers table

okay, now we do a LEFT OUTER JOIN to your data

Code:

select dates.d
     , dayname(dates.d) as weekday
     , count(a.date) as appointments
  from (
       select date_add(
                  concat('$year','-','$month','-01')
                    , interval 10*t.i+u.i day ) as d
         from integers as t
       cross
         join integers as u
        where 10*t.i+u.i 
              between 0 
                  and day(last_day(
                  concat('$year','-','$month','-01')))-1
       ) as dates
left outer
  join appointments as a
    on a.date = dates.d
group
    by dates.d
having count(a.date) > 0    
   or dayofweek(dates.d) in (3,7)

it is the HAVING clause that implements your special conditions ("every date in particular month that have appointment for that day. besides, i also need to select every date, that a doctor works in that month")

notice i discarded your staff table from the query, it wasn't doing anything useful at all

tenma-tenma · #3 (**permalink**) 06-09-07, 10:11

Hi,
thank's a lot for the reply... =)

never heard about the integers table before but thank's =P
i've tried run it in my mysql. it gives me empty result with 100 warnings =P
that shows

Code:

Warning	1292	Truncated incorrect datetime value: '$year-$month-01'

i've read the mysql documentation about this warning and it stated:
"The MySQL server performs only basic checking on the validity of a
date: The ranges for year, month, and day are 1000 to 9999, 00 to
12, and 00 to 31, respectively. Any date containing parts not
within these ranges is subject to conversion to `'0000-00-00''.

do you have an idea what it means? because actually the $year and $month returns 2007 and 06 (valid values)
thank u.. =P

regards,

Lena

r937 · #4 (**permalink**) 06-09-07, 10:28

the concept is sound -- so it's gotta be a php coding error on your part

i'll bet $year and $month are not actually being substituted, and are being passed to mysql as the strings "$year" and "month"

tenma-tenma · #5 (**permalink**) 06-09-07, 10:59

Hi..
yes you're absolutely right...
the $year and $month are passed as string..
now it already works..
thank's a lot =)

tenma-tenma · #6 (**permalink**) 06-14-07, 23:17

Hi... it's me againn..
still dealing with appointment issue.. =)
now i face another problem, what about if the doctor, for example, in wed,27/06/2007 can't work at all, or can only work for 12.00-15.00? i'm quite confuse with this case, since i record each doctor's schedule in dayName.. so, how i access the specific date?
right now, my dbase is:
appointment(appId, staffId, patientId, timeFrom, date, duration)
schedule(staffId, dayId, timeFrom, timeEnd, defaultInterval)
day(dayId, dayName)

thank's for your attention

aschk · #7 (**permalink**) 06-15-07, 04:20

Sounds like you're going to have to change the way your schedule works. In it's current format there is no way to work out whether Mon-Sun is this week (i.e. 11th-17th June). So you're most probably going to have to change the way the structure works. I think we can all agree that timetabling systems are a pain.

Does anyone know of any standard methods/structures that get used in timetabling models?

tenma-tenma · #8 (**permalink**) 06-15-07, 05:42

hi,
thanks for the reply...
so, i should change my dbase structure?

so, should i add 'date' attribute in schedule table, so EVERY doctor schedule's date in the whole year is recorded precisely?
so i should add:
appointment(appId, staffId, patientId, timeFrom, date, duration)
schedule(staffId, dayId, timeFrom, timeEnd, defaultInterval, date)
day(dayId, dayName)
date(dayId, date)

or do you have another idea so that i don't need to change mys dbase structure for this case? because i almost finish everything except this one and another few bugs

thanks for your attention =)

r937 · #9 (**permalink**) 06-15-07, 06:14

i'm not going to be able to help you with your redesign except to say that these tables are not necessary --

day(dayId, dayName)
date(dayId, date)

Internet Services

Web Development

Digital Marketing

Consumer Tech