Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2007
    Posts
    26

    Unanswered: select date in each month, given dayname

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    Last edited by r937; 06-09-07 at 08:33.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2007
    Posts
    26
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2007
    Posts
    26
    Hi..
    yes you're absolutely right...
    the $year and $month are passed as string..
    now it already works..
    thank's a lot =)

  6. #6
    Join Date
    Feb 2007
    Posts
    26
    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

  7. #7
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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?

  8. #8
    Join Date
    Feb 2007
    Posts
    26
    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 =)

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •