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,

asked 26 Dec '12, 16:26

jontom's gravatar image

jontom
16112
accept rate: 0%


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

permanent link

answered 27 Dec '12, 11:39

Thomas%20Duemesnil's gravatar image

Thomas Dueme...
2.5k213460
accept rate: 15%

edited 27 Dec '12, 11:41

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
permanent link

answered 26 Dec '12, 20:47

nico's gravatar image

nico
41236
accept rate: 0%

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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×3

question asked: 26 Dec '12, 16:26

question was seen: 577 times

last updated: 27 Dec '12, 11:41