DECLARE @Temp table
      (
	   idCol int identity(1,1) ,	
       MovementDate date,
       MovementTypeDesc varchar(60),
       CDCNumber varchar(12),
       MovementTime time(7),
       LocationReportMovement varchar(9), 
       OtherLocationCode varchar(9),
	   OtherInstitutionName varchar(50),
       InstReportMovement varchar(9),
	   InstNameReportMovement varchar(100),
       OtherInstitutionCode varchar(9),
	   movementTypeCD int,
	   changeType varchar(30),
	   pickupOrder int
      )
/*
--Get All the transfer in and out patients except bed moves (where from and to locations are same)

*/

INSERT INTO @Temp 
       (
		MovementDate
	  , MovementTypeDesc
	  , CDCNumber
	  , MovementTime
	  , LocationReportMovement
	  , OtherLocationCode
	  , OtherInstitutionName
	  , InstReportMovement
	  , InstNameReportMovement
	  , OtherInstitutionCode
	  , movementTypeCD
	  , changeType
	  , pickupOrder
	  )

SELECT      MovementDate,
            MovementTypeDesc,
            mov.CDCNumber,
            MovementTime,
            LocationReportMovement, 
            OtherLocationCode,
			OtherInstitutionName,
            InstReportMovement,
			InstNameReportMovement,
            OtherInstitutionCode,
			movementTypeCD,
			CASE WHEN movementTypeCD = 30 THEN 'Transfer In' WHEN movementTypeCD = 90 THEN 'Transfer Out' END AS ChangeType,
			RANK() OVER (PARTITION BY mov.CDCNumber  ORDER BY movementDate DESC) AS rankNo	
				
FROM [HCODS].dbo.vSTSExternalMovement  mov 

WHERE
InstReportMovement = '100000000'
AND 
  LTRIM(RTRIM(InstNameReportMovement)) <> LTRIM(RTRIM(OtherInstitutionName))
  AND MovementDate BETWEEN '12/01/2013' AND '06/01/2014'
  AND movementTypeCD IN ('30', '90') --- 30 for Transfer In and 90 for Transfer out
  ORDER BY ChangeType;

/*

 ----Get REST (ignore patients who were already listed in transfer in or out) of the patients with bed moves (where from and to locations are same, 
 ----2 records one for out and one for in will be created in each case so just getting transfer in  i.e movementTypeCd 30 to avoid duplicate records )


*/

 INSERT INTO 
            @Temp(MovementDate, MovementTypeDesc,  CDCNumber, MovementTime,LocationReportMovement, OtherLocationCode, OtherInstitutionName,
            InstReportMovement,InstNameReportMovement,OtherInstitutionCode,movementTypeCD, changeType, pickupOrder)

SELECT      MovementDate,
            MovementTypeDesc,
            mov.CDCNumber,
            MovementTime,
            LocationReportMovement, 
            OtherLocationCode,
			OtherInstitutionName,
            InstReportMovement,
			InstNameReportMovement,
            OtherInstitutionCode,
			movementTypeCD,
			'Bed Move'  AS ChangeType,
			RANK() OVER (PARTITION BY mov.CDCNumber  ORDER BY movementDate DESC) AS rankNo	
FROM [HCODS].dbo.vSTSExternalMovement  mov 
WHERE
InstReportMovement = '100000000'
AND 
LTRIM(RTRIM(InstNameReportMovement)) = LTRIM(RTRIM(OtherInstitutionName))
  AND MovementDate BETWEEN '12/01/2013' AND '06/01/2014'
 AND movementTypeCD ='30' AND mov.CDCNUmber Not IN (SELECT CDCNumber FROM @Temp);
 

------* Actual Query *----
SELECT 
             CONVERT(VARCHAR(10), movementDate, 101) AS movementDate,
	         h.CDCNumber,
	         prof.INMATELASTNAME AS PatientLast
		     , prof.INMATEFIRSTNAME AS PatientFirst
		     , prof.[INMATEHOUSINGAREAID]  + prof.[INMATEBEDNUMBER]  AS Housing,
             h.OtherLocationCode,
             h.InstNameReportMovement,
	         h.OtherInstitutionName,
             h.OtherInstitutionCode,
             fac.LocationCode AS OtherFacilityCode,

	          appt.AppointmentID,  
			  appt.ServiceArea, 
			  appt.appointmentLength, 
			  appt.ProblemSymptom,
	          appt.SourceOfVisit, 
              CONVERT(VARCHAR(10), appt.AppointmentDate, 101) AS AppointmentDate,
			  CONVERT(VARCHAR(5), appt.AppointmentTime, 108) AS AppointmentTime,
			  appt.AppointmentStatus,
			  CONVERT(VARCHAR(10), appt.Compliance, 101) AS Compliance,
			  appt.Location,
			  appt.AppointmentStatusID,
			  appt.LocationID,
			  changeType

  FROM 
    
     @Temp as h 
	 LEFT join   v_AppointmentsLocal appt  ON h.CDCNumber = appt.CDCnumber
     INNER JOIN  [HCODS].[dbo].[vInmateProfile] AS prof ON prof.[ADCNUMBER] = appt.CDCnumber 
     JOIN        v_Facility fac ON h.OtherInstitutionCode = fac.SOMSInstitutionCode

    WHERE    
	   appt.FacilityID = '2'
	     AND ServiceAreaID ='2'
	   ----AND ServiceAreaID IN (@ServiceArea)
       AND (AppointmentStatusID IN (1, 2))  AND h.pickupOrder = 1
	   ORDER BY  h.changeType DESC
	   , 
	   OtherInstitutionName, appt.LocationCode, appt.appointmentStatus DESC

Thanks.