Hi, Forgive my terminology if I

[ouch8s]

Hi,

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

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:

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,

 

[ouch8s]

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

 

[yelworcm]

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

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.