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:
which is working except it returns a separate row for each value in ROOMBOOK.FROMDATE, so I get something like this:
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,
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,
Talk To Other Members
RE: Hi, Forgive my terminology if I
RE: Hi, Forgive my terminology if I
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.