[thelist] creating two-dimensional timetable in ASP/SQLServer

[listman at hazard0us.org]

I did something quite similar for a school recently -  i did it
*free* (no, actually for beer :)) so it doesnt look very pretty
but works :

I have a schedule table that looks like this :
(its got more fields than that but i removed them here...)

name       tday        tstart      tend
---------- ----------- ----------- -----------
ashok      1           900         1000
ashok      2           900         1000
ashok      3           900         930
ashok      4           1200        1300
jim        1           900         1000
jim        2           900         1000
jim        3           900         930
jim        4           1200        1300
jim        5           1200        1300
reeve      1           1200        1300
jim        5           1500        1700
jim        5           1300        1500

note : all my times are integers, i didnt use datetimes...
tstart, tend are time start & time end.
tday is the day of the week.  name is of course...

one report needed was the weekly classroom schedule for a student.
this query :

select count(name) as 'no_of_stud' ,sum(tday) as 'day_of_week',
case when (tstart >= 900 and tend <= 1100)
then 1 else 0 end  as 'c900_1100',
case when (tstart >= 1100 and tend <= 1300)
then 1 else 0 end as 'c1100_1300',
case when (tstart >= 1300 and tend <= 1500)
then 1 else 0 end as 'c1300_1500',
case when (tstart >= 1500 and tend <= 1700)
then 1 else 0 end as 'c1500_1700'
from schedule
Where name='jim'
group by tday, tstart, tend

(note that i have hard-code schedules - not a good idea.)
we use the sqlServer 'case' syntaxt to split the columns...
1 stands for yes & 0 stands for no...
for jim this would return :

no_of_classes day_of_week c900_1100   c1100_1300  c1300_1500  c1500_1700
------------- ----------- ----------- ----------- ----------- -----------
1             1           1           0           0           0
1             2           1           0           0           0
1             3           1           0           0           0
1             4           0           1           0           0
1             5           0           1           0           0 <--
1             5           0           0           1           0 <--
1             5           0           0           0           1 <--

unfortunately it returns 3 rows for thursday (day_of_week = 5),
but we can aggregate it further..

select count(no_of_stud) as 'no_of_classes' ,
     day_of_week,
     sum(c900_1100) as '900-100',
     sum(c1100_1300) as '1100-1300',
     sum(c1300_1500) as '1300-1500',
     sum(c1500_1700) as '1500-1700'
from (
select count(name) as 'no_of_stud' ,sum(tday) as 'day_of_week',
case when (tstart >= 900 and tend <= 1100)
then 1 else 0 end  as 'c900_1100',
case when (tstart >= 1100 and tend <= 1300)
then 1 else 0 end as 'c1100_1300',
case when (tstart >= 1300 and tend <= 1500)
then 1 else 0 end as 'c1300_1500',
case when (tstart >= 1500 and tend <= 1700)
then 1 else 0 end as 'c1500_1700'
from schedule
Where name='jim'
group by tday, tstart, tend
) xTable
group by day_of_week


this will return the correct weekly schedule for 'jim' :

no_of_classes day_of_week 900-100     1100-1300   1300-1500   1500-1700
------------- ----------- ----------- ----------- ----------- -----------
1             1           1           0           0           0
1             2           1           0           0           0
1             3           1           0           0           0
1             4           0           1           0           0
3             5           0           1           1           1


you should easily be able to output this directly from an asp recordset....



HTH
ashok


------
Ashok Hariharan
http://www.unganisha.org
------