The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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.4k203357
accept rate: 16%

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: 558 times

last updated: 27 Dec '12, 11:41