|
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, |
|
You can partition the result set by room number 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', min( ROOMBOOK.FROMDATE ) over (ROOMS.ROOMNUMBER) AS minDate, 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 ... AND ROOMBOOK.FROMDATE = mindate Check the help file for WINDOW functions. There you can find some ideas. Hope This Helps |
|
If everything else is working how about adding MIN and CURRENT DATE? 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', MIN(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' AND ROOMBOOK.FROMDATE >= CURRENT DATE Without a GROUP BY, there is only one group (all the resulting rows) so MIN(ROOMBOOK.FROMDATE) will have the same value for all the rows.
(27 Dec '12, 08:04)
Breck Carter
|
