Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rct1718
Starting Member
1 Post |
 Posted - 2007-12-02 : 14:56:57
|
| I've been working on this query for hours now and to no avail. I've looked at forums, read the class's textbook and studied my notes but I'm completely stumped. The question is as follows:I need a list of all the classes we teach that are held on ONLY both Mondays and Wednesdays and that end after 2:00 PM. I need the class ID, the classroom ID and the start and end times for those classes.Here is a screenshot of the table being used: http://farm3.static.flickr.com/2036/2081722024_0d5c8ba40b_o.jpgThank you. I really appreciate the help. |
|
|
tacket
Starting Member
47 Posts |
Posted - 2007-12-04 : 09:35:05
|
| Start with what you need, that's the select clause. In this case 'select class_id, classroom_id, start_time,end_time'Next the from clause, which is the table(s) you are selecting from'from school_scheduling'ok now the tricky part :) the 'where' clause.where mondayschoolschedule = 'true' and wednesdayschoolschedule = 'true'and dateadd(mm,duration,starttime) > convert(smalldatetime,getdate(),101) + ' ' + '2:00 P.M.'The only tricky part about this is compareing the dates in the 'and clause'. According to your spreadsheet there are only times and SQL cannot just compair times, it needs the dates. That's why I converting 2:00 P.M. to a date and compaired it that way. There are probably a hundred different ways to do this. I'm 99% sure everything is correct up until the 'and' clause. But I guarantee you put that in your quiz or whatever the prof will be impressed :).Phil |
 |
|
|
georgev
Posting Yak Master
122 Posts |
Posted - 2007-12-08 : 09:00:48
|
MSAccess has alovely function called Hour() which extract the hour date part from a datetime value.[CODE]WHERE Hour(aDate) >= 14[/CODE]
George<3Engaged! |
|
|
|
|
|
|
|
|
Need help creating SQL in Access