×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Hi, Forgive my terminology if I

Hi, Forgive my terminology if I

Hi, Forgive my terminology if I

(OP)
Hi,

Forgive my terminology if I get it wrong as I'm just teaching myself SQL. I have the following query:

CODE -->

SELECT ROOMS.ROOMNUMBER AS 'Room Number',
       ROOMS.ROOMSTATUS AS 'Status of Room',
       FOLIOHD.CHECKIN AS 'Current C/In Date',
       FOLIOHD.CHECKOUT AS 'Current C/Out Date',
       ROOMS.LASTOCCDATE AS 'Last C/Out Date',
       FOLIOHD.HOUSENOTE AS 'Housekeeping Note',
       ROOMBOOK.FROMDATE AS 'Next C/In Date',
       ROOMBOOK.TODATE AS 'Next C/Out Date'
 
FROM ROOMS
LEFT OUTER JOIN FOLIOHD ON ROOMS.ROOMNUMBER = FOLIOHD.ROOMNUMBER
LEFT OUTER JOIN HISTHD ON ROOMS.LASTFOLIO = HISTHD.NUMBER
JOIN ROOMBOOK ON ROOMS.ROOMNUMBER = ROOMBOOK.ROOMNUMBER
 
WHERE ROOMS.ROOMNUMBER <> '103' AND  ROOMS.ROOMNUMBER <> '110' AND  ROOMS.ROOMNUMBER <> '210' AND  ROOMS.ROOMNUMBER <> '227' AND  ROOMS.ROOMNUMBER <> '228' 

which is working except it returns a separate row for each value in ROOMBOOK.FROMDATE, so I get something like this:

CODE -->

Room Number	Status of Room	Current C/In Date	Current C/Out Date	Last C/Out Date	Housekeeping Note	Next C/In Date	Next C/Out Date
104	O	26/12/12	27/12/12	26/12/12		27/12/12	01/01/13
104	O	26/12/12	27/12/12	26/12/12		02/01/13	04/01/13
104	O	26/12/12	27/12/12	26/12/12		04/01/13	05/01/13
104	O	26/12/12	27/12/12	26/12/12		05/01/13	19/01/13
104	O	26/12/12	27/12/12	26/12/12		19/01/13	06/04/13
104	O	26/12/12	27/12/12	26/12/12		06/04/13	13/04/13
104	O	26/12/12	27/12/12	26/12/12		26/04/13	28/04/13
104	O	26/12/12	27/12/12	26/12/12		19/07/13	20/07/13
104	O	26/12/12	27/12/12	26/12/12		11/08/13	12/08/13
105	V			24/12/12		26/12/12	28/12/12
105	V			24/12/12		28/12/12	29/12/12
105	V			24/12/12		29/12/12	30/12/12
105	V			24/12/12		30/12/12	01/01/13
105	V			24/12/12		02/01/13	04/01/13
105	V			24/12/12		05/01/13	19/01/13
105	V			24/12/12		19/01/13	06/04/13
105	V			24/12/12		06/04/13	13/04/13
105	V			24/12/12		26/04/13	28/04/13
105	V			24/12/12		19/07/13	20/07/13
105	V			24/12/12		11/08/13	12/08/13
106	V			26/12/12		26/12/12	27/12/12
106	V			26/12/12		27/12/12	01/01/13
106	V			26/12/12		02/01/13	04/01/13
106	V			26/12/12		11/01/13	19/01/13
106	V			26/12/12		19/01/13	06/04/13
106	V			26/12/12		06/04/13	13/04/13 


I only want it to return the earliest value for ROOMBOOK.FROMDATE (ie: the next check/in, not all future checkins). How do I filter for this?

Thanks,

RE: Hi, Forgive my terminology if I

(OP)
Well, I'm off to a great start. I can't even figure out how to edit my post. Sorry about the title.

RE: Hi, Forgive my terminology if I

you'll just need to do a subquery to get the data you want. Something like:

CODE

SELECT ROOMS.ROOMNUMBER AS 'Room Number',
       ROOMS.ROOMSTATUS AS 'Status of Room',
       FOLIOHD.CHECKIN AS 'Current C/In Date',
       FOLIOHD.CHECKOUT AS 'Current C/Out Date',
       ROOMS.LASTOCCDATE AS 'Last C/Out Date',
       FOLIOHD.HOUSENOTE AS 'Housekeeping Note',
       ROOMBOOK.FROMDATE AS 'Next C/In Date',
       ROOMBOOK.TODATE AS 'Next C/Out Date'
 
FROM ROOMS
LEFT OUTER JOIN FOLIOHD ON ROOMS.ROOMNUMBER = FOLIOHD.ROOMNUMBER
LEFT OUTER JOIN HISTHD ON ROOMS.LASTFOLIO = HISTHD.NUMBER
JOIN ROOMBOOK ON ROOMS.ROOMNUMBER = ROOMBOOK.ROOMNUMBER 

join (select ROOMNUMBER, min(FROMDATE) as NEXTDATE from ROOMBOOK where FROMDATE > sysdate()) nextbooking on ROOMBOOK.ROOMNUMBER = nextbooking.ROOMNUMBER and ROOMBOOK.FROMDATE = nextbooking.FROMDATE

WHERE ROOMS.ROOMNUMBER <> '103' AND  ROOMS.ROOMNUMBER <> '110' AND  ROOMS.ROOMNUMBER <> '210' AND  ROOMS.ROOMNUMBER <> '227' AND  ROOMS.ROOMNUMBER <> '228' 

I left it as an inner join. You may want to try a left join to get the rooms with no current future booking.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close