Re: need help forming sql query...

Hi Tom,
Thank you for your suggestion.
Is it possible to modify the sql statement so that rooms that have NOT been reserved will be returned from the query.

The rooms that have not been reserved will NOT be in the 'room_reservations' table.

For example, if room 22 has not been reserved at all, the querry still should return

  Room       Total_hr_reserved
   20          1
   21          2
   22          0  (NOTE: room 22 is not reserved)

  as opposed to
   Room       Total_hrs_Reserved
    20         1
    22         2

Again, hopefully all this can be done with one sql statment.. Thank you in advance.

In article <AVlW5.3072$tR6.567081_at_news.uswest.net>,   "Tom Leary" <tleary1_at_uswest.net> wrote:
> Just remember that the unit for date fields is days, so if
 date1=12/01/2000
> 13:00, and date2=12/02/2000 19:00, then date2 - date1 = 1.25 (days).
 To
> convert to another time format, like minutes, multiply by the
 conversion
> factor: 1.25 days = 1.25 d * 24 h/d = 30 hours = 1.25 d * 24 h/d *
 60 m/h
> = 1800 min
>
> So, your query for total minutes reserved for each room is
>
> select room_id "Room", sum( (end_time - start_time) * 24 * 60 )
> "Total_Min_Reserved"
> from room_reservations
> group by room_id
>
> HTH,
>
> DBA Tom
>
> <mcharon_at_yahoo.com> wrote in message

 news:90cg1o$97d$1_at_nnrp1.deja.com...
> > suppose i create two tables
> >
> > /*this table describes the room */
> > creat room (
> > room_id NUMBER not null/* this is the room number */
> > )
> >
> > /*this table describes room reservations */
> > create room_reservations (
> > room_id NUMBER ; /* this is the room number */
> > start_time DATE; /*the room is reserved from 'start_time' to
 'end_time'
> > end_time DATE; /*the room is reserved from 'start_time' to
 'end_time'
> > )
> >
> > How do I write sql statement to find out total hrs reserved for each
> > room?? i am hoping to be able to do this withh one sql statement and
 by
> > not adding anymore columns to the tables.
> > if the query turns out to be a complicated sql statement, I would
> > appreciate some sort of explanation as well, if it's not too much
> > trouble.
> >
> > For example, if the row entries for room table are
> > room_id
> > 20
> > 21
> > 22
> > And row entries for room_reservations table are
> > room_id start_time end_time
> > 20 12pm 1pm
> > 21 8pm 9pm
> > 21 10pm 11pm
> >
> > The desired sql query should return
> > room_id total_hrs_reserved
> > 20 1
> > 21 2
> > 22 0
> >
> >
> > I am stuck on this for over a week now;; any help will be greatly
> > appreciated.
> >
> > thank you in advance
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
>

--
thank you in advance


Sent via Deja.com http://www.deja.com/
Before you buy.