Re: need help forming sql query...

Never did see a posting of actual code ... so ...

select a.room_id,24*nvl(sum(b.end_time-b.start_time),0) sumt from room a,room_resv b
where a.room_id=b.room_id(+)
group by a.room_id
/

Now, this wouldn't be a homework assignment, would it? jc

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.
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sun Dec 10 2000 - 01:14:32 CST