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/13I 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,