CREATE PROCEDURE dbo.OnlineFindPatientInfo @ACprNr VARCHAR(50)  AS
SET NOCOUNT ON
/*
    Created by Troels Buch Okt 2009
    
    Changes:   
	TB20100519: Added @dattenow
	TB20100303: Added Order BY
	TB20091125: Added departuredate
	

    NOTES: 	- 
    Used in Patienthotel mode
*/

DECLARE @ACprNrStr VARCHAR(50)
DECLARE @ACprNrStr2 VARCHAR(50)
DECLARE @dattenow  VARCHAR(8)

SET @ACprNrStr = SUBSTRING(@ACprNr, 1, 6) + '-' + SUBSTRING(@ACprNr, 7, 9)
SET @ACprNrStr2 = SUBSTRING(@ACprNr, 1, 6) + SUBSTRING(@ACprNr, 8, 9)
SET @dattenow = CONVERT(VARCHAR(8), GETDATE() , 112 )

--print @ACprNrStr
--print @ACprNrStr2

SELECT Firstname, Lastname, Altresno, Status, res.Referencenumber, rrl.arrivaldate, 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
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 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 res.Referencenumber IN ( SELECT Referencenumber FROM Reservationaddress WITH (NOLOCK) 
INNER JOIN Reservationaddressxtab WITH (NOLOCK) ON Reservationaddress.AddressNumber = Reservationaddressxtab.AddressNumber 
WHERE altresno = @ACprNr OR altresno = @ACprNrStr OR altresno = @ACprNrStr2) 
AND altresno <> '' AND altresno <> '-' 
AND @dattenow <= rrl.departuredate ORDER BY Status desc


GO
