Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: need help forming sql query...

Re: need help forming sql query...

From: Tom Leary <tleary1_at_uswest.net>
Date: Sun, 3 Dec 2000 00:42:13 -0800
Message-ID: <AVlW5.3072$tR6.567081@news.uswest.net>

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.
Received on Sun Dec 03 2000 - 02:42:13 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US