Thread: select date in each month, given dayname

1. Registered User
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..

2. SQL Consultant
Join Date
Apr 2002
Location
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 (
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.

3. Registered User
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'`
"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. SQL Consultant
Join Date
Apr 2002
Location
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"

5. Registered User
Join Date
Feb 2007
Posts
26
Hi..
yes you're absolutely right...
the \$year and \$month are passed as string..
thank's a lot =)

6. Registered User
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)

7. Registered User
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. Registered User
Join Date
Feb 2007
Posts
26
hi,
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?
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

9. SQL Consultant
Join Date
Apr 2002
Location
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)

Posting Permissions

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