CREATE PROCEDURE dbo.OnlineFindReservationCompany @ADateNow DATETIME, @ACustomerNumber VARCHAR(50) AS
SET NOCOUNT ON
/*
    Created by Troels Buch Sep 2009
    
    Changes:   
	TB20100223 - Added Dates to reservationroomline
	TB20091023 - Added Dates to select  
	

    NOTES: 	- 
    Used in Patienthotel mode
*/
/*
SELECT DISTINCT Firstname, Lastname, Altresno, Status, res.Referencenumber, rrl.arrivaldate, rrl.departuredate, (SELECT TOP 1 rooms.number FROM rooms (nolock) 
INNER JOIN reservationroomline (NOLOCK) ON reservationroomline.roomnumber = rooms.roomnumber WHERE reservationroomline.linenumber = rrl.linenumber) AS Number, 
(SELECT TOP 1 reservationnotes.lists FROM reservationnotes (nolock) INNER JOIN reservations (NOLOCK) ON reservations.referencenumber = reservationnotes.referencenumber 
WHERE reservations.GuestNumber = 0 AND NotePage = 1 
AND reservations.referencenumber = res.referencenumber) AS Cpr 
FROM Reservationaddress (NOLOCK) INNER JOIN Reservationaddressxtab (NOLOCK) ON Reservationaddress.AddressNumber = Reservationaddressxtab.AddressNumber 
AND AddressType = 1 INNER JOIN Reservations res (NOLOCK) ON res.Referencenumber = Reservationaddressxtab.Referencenumber 
AND CONVERT(DATETIME, ArrivalDate, 112) <= @ADateNow AND CONVERT(DATETIME, DepartureDate, 112) >= @ADateNow 
AND res.guestnumber = reservationaddressxtab.guestnumber 
INNER JOIN reservationroomlinextab (nolock) ON res.referencenumber = reservationroomlinextab.referencenumber 
AND res.referencenumber = res.Referencenumber 
INNER JOIN reservationroomline rrl (nolock) ON rrl.linenumber = reservationroomlinextab.linenumber 
WHERE res.Referencenumber IN ( SELECT Referencenumber FROM Reservationaddress (NOLOCK) 
INNER JOIN Reservationaddressxtab (NOLOCK) ON Reservationaddress.AddressNumber = Reservationaddressxtab.AddressNumber 
WHERE Link = @ACustomerNumber AND Status < 6 ) ORDER BY Status, Lastname*/

SELECT DISTINCT Firstname, Lastname, Altresno, Status, res.Referencenumber, rrl.arrivaldate, rrl.linenumber, res.guestnumber, rrl.departuredate, (SELECT TOP 1 rooms.number FROM rooms WITH (nolock) 
INNER JOIN reservationroomline WITH (NOLOCK) ON reservationroomline.roomnumber = rooms.roomnumber WHERE reservationroomline.linenumber = rrl.linenumber) AS Number, 
(SELECT TOP 1 reservationnotes.lists FROM reservationnotes WITH (nolock) INNER JOIN reservations WITH (NOLOCK) ON reservations.referencenumber = reservationnotes.referencenumber 
WHERE reservations.GuestNumber = 0 AND NotePage = 1 
AND reservations.referencenumber = res.referencenumber) AS Cpr 
FROM Reservationaddress WITH (NOLOCK) INNER JOIN Reservationaddressxtab WITH (NOLOCK) ON Reservationaddress.AddressNumber = Reservationaddressxtab.AddressNumber 
AND AddressType = 1 INNER JOIN Reservations res WITH (NOLOCK) ON res.Referencenumber = Reservationaddressxtab.Referencenumber 
AND CONVERT(DATETIME, ArrivalDate, 112) <= @ADateNow AND CONVERT(DATETIME, DepartureDate, 112) >= @ADateNow 
AND res.guestnumber = reservationaddressxtab.guestnumber 
INNER JOIN reservationroomlinextab WITH (nolock) ON res.referencenumber = reservationroomlinextab.referencenumber 
AND res.referencenumber = res.Referencenumber 
INNER JOIN reservationroomline rrl WITH (nolock) ON rrl.linenumber = reservationroomlinextab.linenumber 
WHERE CONVERT(DATETIME, rrl.ArrivalDate, 112) <= @ADateNow AND CONVERT(DATETIME, rrl.DepartureDate, 112) >= @ADateNow 
AND res.Referencenumber IN ( SELECT Referencenumber FROM Reservationaddress WITH (NOLOCK) 
INNER JOIN Reservationaddressxtab WITH (NOLOCK) ON Reservationaddress.AddressNumber = Reservationaddressxtab.AddressNumber 
WHERE Link = @ACustomerNumber AND Status < 6 ) ORDER BY Status, Lastname
GO
