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,
RE: Hi, Forgive my terminology if I
RE: Hi, Forgive my terminology if I
CODE
I left it as an inner join. You may want to try a left join to get the rooms with no current future booking.