I'm trying to create a sql query that populates a 'grid' on my web page.I have it partially working but there are a few parts I cant quite figure out.Basically my table has a row for each 'name' (person) and that row contains a StartTime and EndTime.My Select currently puts a 1 in any 'hour' on or between the Start and End times. However, it doesnt work if that range hits or overlaps 00:00. Can someone help me adjust this so it will know how to handle the 00:00 overlap ?current query:Select Name, StartTime, EndTime,case when '00' between left(StartTime,2) and left(endtime,2) then 1 else 0 end as "00:00",case when '00' between left(StartTime,2) and left(endtime,2) then 1 else 0 end as "00:00",case when '01' between left(StartTime,2) and left(endtime,2) then 1 else 0 end as "01:00",case when '02' between left(StartTime,2) and left(endtime,2) then 1 else 0 end as "02:00",case when '03' between left(StartTime,2) and left(endtime,2) then 1 else 0 end as "03:00",case when '04' between left(StartTime,2) and left(endtime,2) then 1 else 0 end as "04:00",case when '05' between left(StartTime,2) and left(endtime,2) then 1 else 0 end as "05:00",case when '06' between left(StartTime,2) and left(endtime,2) then 1 else 0 end as "06:00",case when '07' between left(StartTime,2) and left(endtime,2) then 1 else 0 end as "07:00",case when '08' between left(StartTime,2) and left(endtime,2) then 1 else 0 end as "08:00",case when '09' between left(StartTime,2) and left(endtime,2) then 1 else 0 end as "09:00",case when '10' between left(StartTime,2) and left(endtime,2) then 1 else 0 end as "10:00",case when '11' between left(StartTime,2) and left(endtime,2) then 1 else 0 end as "11:00",case when '12' between left(StartTime,2) and left(endtime,2) then 1 else 0 end as "12:00",case when '13' between left(StartTime,2) and left(endtime,2) then 1 else 0 end as "13:00",case when '14' between left(StartTime,2) and left(endtime,2) then 1 else 0 end as "14:00",case when '15' between left(StartTime,2) and left(endtime,2) then 1 else 0 end as "15:00",case when '16' between left(StartTime,2) and left(endtime,2) then 1 else 0 end as "16:00",case when '17' between left(StartTime,2) and left(endtime,2) then 1 else 0 end as "17:00",case when '18' between left(StartTime,2) and left(endtime,2) then 1 else 0 end as "18:00",case when '19' between left(StartTime,2) and left(endtime,2) then 1 else 0 end as "19:00",case when '20' between left(StartTime,2) and left(endtime,2) then 1 else 0 end as "20:00",case when '21' between left(StartTime,2) and left(endtime,2) then 1 else 0 end as "21:00",case when '22' between left(StartTime,2) and left(endtime,2) then 1 else 0 end as "22:00",case when '23' between left(StartTime,2) and left(endtime,2) then 1 else 0 end as "23:00"From [Schedules]
SQL Select query help
Posted - 2008-03-20 : 20:35:42
