SELECT AdmDate AS TransDate,
       his.IDNum AS IdNum,
       REPLACE(SUBSTRING(ChiefComplaint, 1, DATALENGTH(ChiefComplaint)), CHAR(10), '<br />') AS ChiefComplaint,
       HIS.FinalDiagnosis AS [Final Diagnosis],
       'Select IDNumber to show medicines below' AS Medications,
       REPLACE(SUBSTRING(PROF.Others, 1, DATALENGTH(PROF.Others)), CHAR(10), '<br />') AS Premarks,
       DOC.Lastname + ', ' + DOC.Firstname AS DocName,
       'IN' AS RegType
FROM [Patient_Data].[dbo].[tbPatient] AS PAT
     LEFT OUTER JOIN [Patient_Data].[dbo].[tbPatientHistory] AS HIS ON HIS.IDNum = PAT.IdNum
     LEFT OUTER JOIN [Station].[dbo].[tbNurseProfile] AS PROF ON HIS.IDNum = PROF.IdNum
     LEFT OUTER JOIN Build_file..tbCoDoctor DOC ON PAT.AttendingDr1 = DOC.DoctorID
WHERE PAT.HospNum = '544524'
UNION ALL
SELECT AdmDate AS TransDate,
       his.IDNum AS IdNum,
       REPLACE(SUBSTRING(ChiefComplaints, 1, DATALENGTH(ChiefComplaints)), CHAR(10), '<br />') AS ChiefComplaint,
       HIS.Findings AS [Final Diagnosis],
       HIS.Medications,
       REPLACE(SUBSTRING(PAT.Remarks, 1, DATALENGTH(PAT.Remarks)), CHAR(10), '<br />') AS Premarks,
       DOC.Lastname + ', ' + DOC.Firstname AS DocName,
       'OUT' AS RegType
FROM [Patient_Data].[dbo].[tbOutPatient] AS PAT
     LEFT OUTER JOIN [Patient_Data].[dbo].[tbOutPatientHistory] AS HIS ON HIS.IDNum = PAT.IdNum
     LEFT OUTER JOIN [Station].[dbo].[tbNurseProfile] AS PROF ON HIS.IDNum = PROF.IdNum
     LEFT OUTER JOIN Build_file..tbCoDoctor DOC ON PAT.DoctorID1 = DOC.DoctorID
WHERE PAT.HospNum = '544524'
UNION ALL
SELECT consdate AS TransDate,
       his.IDNum AS IdNum,
       REPLACE(SUBSTRING(ChiefComplaint, 1, DATALENGTH(ChiefComplaint)), CHAR(10), '<br />') AS ChiefComplaint,
       '' AS [Final Diagnosis],
       '' AS Medications,
       REPLACE(SUBSTRING(Remarks, 1, DATALENGTH(Remarks)), CHAR(10), '<br />') AS Premarks,
       DOC.Lastname + ', ' + DOC.Firstname AS DocName,
                                              CASE
                                                  WHEN CHARINDEX('B', his.idnum) > 0
                                                  THEN 'OUT'
                                                  ELSE 'IN'
                                              END AS RegType
FROM doctors..Consultation AS HIS
     LEFT OUTER JOIN Build_file..tbCoDoctor DOC ON HIS.DoctorID = DOC.DoctorID
WHERE his.HospNum = '544524'
ORDER BY TransDate DESC;