/* Created  by Acmeware, Inc., All Rights Reserved					*/
/*--------------------------------------------------------------------------------------*/
/* This function returns the location of an Inpatient at any point in his/her stay	*/
/*											*/
/* Parameters: 										*/
/*	@VisitID: A patient VisitID (i.e., URN or internal Account Number)		*/
/*	@CheckDate: Date/time when patient location is to be determined			*/
/*											*/
/*	Return Value: LocationID varchar (30)						*/
/*											*/
/* Note: 										*/
/*--------------------------------------------------------------------------------------*/
/*											*/
/* THIS SOFTWARE IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED WARRANTIES, 	*/
/* INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF MERCHANTIBILITY AND 	*/
/* FITNESS FOR A PARTICULAR PURPOSE.							*/



CREATE        FUNCTION dbo.fxFindLocation (@VisitID Varchar(30), @CheckDate datetime)  

RETURNS varchar(30) AS  

BEGIN 
DECLARE @R varchar(30)
SET @R = 
	(SELECT  AVE.LocationID
	FROM [livedb].dbo.AdmVisitEvents AVE
	WHERE 
		AVE.VisitID = @VisitID
		AND AVE.EffectiveDateTime = 
			(SELECT MAX(AVE1.EffectiveDateTime)
			FROM [livedb].dbo.AdmVisitEvents AVE1
				WHERE (AVE.SourceID = AVE1.SourceID)
				AND (AVE.VisitID = AVE1.VisitID)
				AND AVE1.LocationID IS NOT NULL
				AND (AVE1.EffectiveDateTime <= @CheckDate))
		AND AVE.EventDateTime = 
			(SELECT MAX(AVE1.EventDateTime)
			FROM [livedb].dbo.AdmVisitEvents AVE1
				WHERE (AVE.SourceID = AVE1.SourceID)
				AND (AVE.VisitID = AVE1.VisitID)
				AND AVE1.LocationID IS NOT NULL
				AND (AVE.EffectiveDateTime = AVE1.EffectiveDateTime)
				AND (AVE1.Code NOT LIKE 'UNDO%')
				AND (AVE1.Description NOT LIKE '%%'))
		AND AVE.EventSeqID = 
			(SELECT MAX(AVE1.EventSeqID)
			FROM [livedb].dbo.AdmVisitEvents AVE1
				WHERE (AVE.SourceID = AVE1.SourceID)
				AND (AVE.VisitID = AVE1.VisitID)
				AND AVE1.LocationID IS NOT NULL
				AND (AVE.EffectiveDateTime = AVE1.EffectiveDateTime)
				AND (AVE.EventDateTime  = AVE1.EventDateTime)
				AND (AVE1.Code NOT LIKE 'UNDO%')
				AND (AVE1.Description NOT LIKE '%%'))
	AND AVE.LocationID IS NOT NULL)

RETURN @R

END